Import or Insert values with indentiy column

Recently I need to dump some data from test DB instance to production DB instance, but one table has an identity column and I didn’t want it to be generated by DB as this column are foreign key of other tables, so I need to dump exactly the same value to the identity column. Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT *
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And got this error message:

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

The correct way is as following:

1) set identity insert to ON:

SET IDENTITY_INSERT db1.dbo.table1 ON

2) specify column list, for example:

INSERT INTO
DB1.dbo.TABLE (column1, column2, column3)
SELECT column1, column2, column3
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column
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