April 6, 2017 at 12:13 pm
im currently testing loading some test data however, when i switch the identity insert on and run the insert statement i get an error
Msg 544, Level 16, State 1, Line 8
Cannot insert explicit value for identity column in table 'SOMETABLENAME' when IDENTITY_INSERT is set to OFF.
however i have set the identity insert to on
Steps followed
1. run
set IDENTITY_INSERT SOMETABLENAME ON;
2. result
Command(s) completed successfully.
3. run
insert into sometable (id_col,col1,col2,col3)
select id_col,col1,col2,col3 from someothertable
4 error appears
Msg 544, Level 16, State 1, Line 8
Cannot insert explicit value for identity column in table 'SOMETABLENAME' when IDENTITY_INSERT is set to OFF.
I have googles around and followed the advice however, nothing appears to work
cheers
***The first step is always the hardest *******
April 6, 2017 at 12:18 pm
SGT_squeequal - Thursday, April 6, 2017 12:13 PMim currently testing loading some test data however, when i switch the identity insert on and run the insert statement i get an errorMsg 544, Level 16, State 1, Line 8
Cannot insert explicit value for identity column in table 'SOMETABLENAME' when IDENTITY_INSERT is set to OFF.however i have set the identity insert to on
Steps followed
1. run
set IDENTITY_INSERT SOMETABLENAME ON;
2. result
Command(s) completed successfully.
3. run
insert into sometable (id_col,col1,col2,col3)
select id_col,col1,col2,col3 from someothertable
4 error appearsMsg 544, Level 16, State 1, Line 8
Cannot insert explicit value for identity column in table 'SOMETABLENAME' when IDENTITY_INSERT is set to OFF.I have googles around and followed the advice however, nothing appears to work
cheers
Don't run the turning on of that property separately. It needs to be part of the overall insert script, and similarly, turning it off should be the last part of the insert.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2017 at 12:25 pm
cheers for the reply but i have tried that also and i still get the error, whats more strange is, i know that only 1 table can have identity insert on switched on at a time within a session so to check the identity insert is on i try to switch it on for another table and i get what i expected, an error informing me i cant switch it on because its already on for sometable.
***The first step is always the hardest *******
April 6, 2017 at 12:30 pm
youll need basically three commands for each identity insert, because you have to toggle the setting off on one table, before you set it ON for the next.
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1 (IdentityID,ColumnList) SELECT Row_number(),ColumnList FROM SomeSource
SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table2 ON
INSERT INTO Table2 (IdentityID,ColumnList) SELECT Row_number(),ColumnList FROM SomeSource2
SET IDENTITY_INSERT Table2 OFF
Lowell
April 6, 2017 at 12:32 pm
Lowell,
Is there a simple query to determine what table has that property turned on ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2017 at 12:40 pm
Cheers Guys,
Lowell,
i am executing my script exactly in the way you explain however, i still get the error
Cannot insert explicit value for identity column in table 'sometable' when IDENTITY_INSERT is set to OFF.
, im scratching my head on this one, i thought it could be permissions based but i have correct permissions to alter the table? any ideas
cheers
***The first step is always the hardest *******
April 6, 2017 at 12:40 pm
sgmunson - Thursday, April 6, 2017 12:32 PMLowell,Is there a simple query to determine what table has that property turned on ?
i know it is a per-session setting, but offhand, I cannot say I've ever seen a detection mechanism; my knee-jerk reaction was to check the MSDN SESSIONPROPERTY to see if an attributed existed, but no...my google-fu showed me basic try-catch ides for detection.
Lowell
April 6, 2017 at 12:45 pm
SGT_squeequal - Thursday, April 6, 2017 12:40 PMCheers Guys,Lowell,
i am executing my script exactly in the way you explain however, i still get the errorCannot insert explicit value for identity column in table 'sometable' when IDENTITY_INSERT is set to OFF.
, im scratching my head on this one, i thought it could be permissions based but i have correct permissions to alter the table? any ideas
cheers
can you show the exact code you are running; so far it was all pseudocode...INSERT RUN...
Lowell
April 6, 2017 at 12:55 pm
unfortunately i cant, i must be missing something, if i create 2 new tables and run my queires in the same way everything works fine, so im guessing its something with the table im trying to insert into, the only thing i can thing of is permissions.
this works
create table testing (col1 int identity(1,1),col2 varchar(10),col3 varchar(10))
set identity_insert testing on
insert into testing (col1,col2,col3)
select id,col1,col2 from XXXTEST
set identity_insert testing off
There is nothing im doing different to my actual script, the syntax is exactly the same, any ideas?
***The first step is always the hardest *******
April 6, 2017 at 1:07 pm
Lowell,
Here's something I found on Stack Overflow: http://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2017 at 1:18 pm
cheers guys, i found my issue, used try catch and realized i have a typo mistake in set identity table and the table im inserting into long table name i noticed the mistake, one ae the other ea My BAD, i knew i was missing😀 something 🙂
***The first step is always the hardest *******
April 6, 2017 at 1:22 pm
SGT_squeequal - Thursday, April 6, 2017 12:55 PMunfortunately i cant, i must be missing something, if i create 2 new tables and run my queires in the same way everything works fine, so im guessing its something with the table im trying to insert into, the only thing i can thing of is permissions.this works
create table testing (col1 int identity(1,1),col2 varchar(10),col3 varchar(10))
set identity_insert testing on
insert into testing (col1,col2,col3)
select id,col1,col2 from XXXTESTset identity_insert testing off
There is nothing im doing different to my actual script, the syntax is exactly the same, any ideas?
I'm still thinking you are showing pseudocode and not actual code.
the things i can think of would be:
using a stack of dynamic queries:
each query is their own session, so this would fail:EXECUTE('SET IDENTITY_INSERT Table1 ON')
EXECUTE('INSERT INTO Table1 (IdentityID,ColumnList) SELECT Row_number(),ColumnList FROM SomeSource')
EXECUTE('SET IDENTITY_INSERT Table1 OFF')
and this would work:EXECUTE('SET IDENTITY_INSERT Table1 ON;
INSERT INTO Table1 (IdentityID,ColumnList) SELECT Row_number(),ColumnList FROM SomeSource;
SET IDENTITY_INSERT Table1 OFF;')
it might be possible that there is multiple tables with the same name, and the schema is defaulting to soemthign unexpected, instead of dbo.
make sure we preface the schemaname.tablename:set identity_insert dbo.testing on
insert into dbo.testing (col1,col2,col3)
select id,col1,col2 from dbo.XXXTEST
set identity_insert dbo.testing off
Lowell
April 6, 2017 at 1:30 pm
Cheers Lowell, my insert works perfect now data loaded
cheers for your help
***The first step is always the hardest *******
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply