October 25, 2016 at 9:10 am
How do you get SQL Server to assign the next identity to a query? I am getting a NULL not allowed for the identity column Sales_Organization_ID.
insert into ATTU_Sales_organization ([Sales_Organization_CD]
,[Sales_Organization_TX]
,[Active_SW]
,[Load_Last_TM]
,[Load_First_TM]
)
select so.[Sales_Organization] as [Sales_Organization_CD]
,so.[Sales_Organization] as [Sales_Organization_TX]
,'Y' as [Active_SW]
,getdate() as [Load_Last_TM]
,getdate() as [Load_First_TM]
from #so so
left join ATTU_Sales_organization aso
on so.Sales_organization = aso.Sales_Organization_CD
where aso.Sales_Organization_CD is null
CREATE TABLE [dbo].[ATTU_Sales_organization](
[Sales_Organization_ID] [int] IDENTITY(1,1) NOT NULL,
[Sales_Organization_CD] [nvarchar](50) NOT NULL,
[Sales_Organization_TX] [nvarchar](200) NULL,
[Active_SW] [char](1) NULL,
[Load_Last_TM] [datetime] NULL,
[Load_First_TM] [datetime] NULL,
CONSTRAINT [PK_ATTU_Sales_organization_1] PRIMARY KEY CLUSTERED
(
[Sales_Organization_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
October 25, 2016 at 9:32 am
SQL Server will assign the value automatically. Are you sure that the column has the identity property assigned correctly?
At first, I thought another possibility would be having SET IDENTITY_INSERT ON, but that gives a different error.
October 25, 2016 at 10:11 am
Are you sure you read that error correctly and it wasn't complaining about the organization CD being null?
I would just run the SELECT underlying the INSERT, so that you can check. I suspect something is failing one of the constraints.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2016 at 11:31 am
That is what I understood.. and I think the create table has it set up correctly.. in my opening post.. Here is the error message:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Sales_Organization_ID', table 'SCCOE_Order_Progression.dbo.ATTU_Sales_organization'; column does not allow nulls. INSERT fails.
The statement has been terminated.
October 25, 2016 at 11:53 am
What's the result from this query?
SELECT t.name, c.name, c.is_identity
FROM SCCOE_Order_Progression.sys.columns c
JOIN SCCOE_Order_Progression.sys.tables t ON c.object_id = t.object_id
JOIN SCCOE_Order_Progression.sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND t.name = 'ATTU_Sales_organization'
AND c.name = 'Sales_Organization_ID'
October 25, 2016 at 2:51 pm
Luis Cazares (10/25/2016)
What's the result from this query?
SELECT t.name, c.name, c.is_identity
FROM SCCOE_Order_Progression.sys.columns c
JOIN SCCOE_Order_Progression.sys.tables t ON c.object_id = t.object_id
JOIN SCCOE_Order_Progression.sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
AND t.name = 'ATTU_Sales_organization'
AND c.name = 'Sales_Organization_ID'
The result is...
name name is_identity
ATTU_Sales_organizationSales_Organization_ID1
October 25, 2016 at 3:11 pm
Now, that's weird. Have you tried reseeding the identity value?
I'd post the syntax but I'm on my phone right now.
October 25, 2016 at 3:32 pm
Is SCCOE_Order_Progression the database you're running all this in? Wondering if there's a trigger on that table inserting into a table of the same name in another DB.
Far-fetched, but so far the symptoms are quite strange. For that matter, probably worth checking for triggers anyway.
Cheers!
October 29, 2016 at 9:28 am
No trigger.. I copied the data out ....minus ID.. dropped the table and re-created.. copied the data back in and it is happy now. Still not sure what happened.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply