How to insert records to a table with an identity column

If you have a table with an identity column, you can’t just do a simple insert.  That identity column needs to be excluded from your insert statement.

There are times when you need to keep the ID values during your insert.  Like moving data between environments.

Here is the SQL you need to allow your inserts to work.
First turn on identity_insert

SET IDENTITY_INSERT <TABLENAME> ON;

Then do your insert.

Then turn off identity_insert

SET IDENTITY_INSERT <TABLENAME> OFF;
[tweetmeme only_single=”false”]
Advertisements

Got something to add?

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