IDENTITY_INSERT not working

Posted: 2013-03-01 in Sql
SET IDENTITY_INSERT tableA ON
insert into tableA

select *
from anotherdb..tableA
SET IDENTITY_INSERT tableA OFF

Msg 8101, Level 16, State 1, Line 5

An explicit value for the identity column in table ‘tablename’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Simple even with identity insert on you still have to list the columns

 insert into tableA (a , b, c)
 select a , b, c from anotherdb..tableA

Actually if you read the error message it actually tells you but it gets me all the time 😀

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s