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


Then do your insert.

Then turn off identity_insert

[tweetmeme only_single=”false”]

Got something to add?

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s