About insert identity column

0
votes

I need to merge some data (include identity column) from other server to my server without interrupting my service running. I export the data from the other server and insert into to my server like this:

SET IDENTITY_INSERT tbl_content ON

... insert command ...

SET IDENTITY_INSERT tbl_content OFF

However, if I set the IDENTITY_INSERT on, other applications inserting data into the database without identity column will get an error

Explicit value must be specified for identity column in table '....' either when IDENTITY_INSERT is set to ON

How can I insert identity column without interrupting other applications?

1
c#sqlsql-servervb.netsql-server-2008
Not clear what you are asking. Other sessions won't suddenly start getting that error message in the circumstances you describe. This is a session option not a table property. - Martin Smith
@MartinSmith Yes, you're right. Sorry i haven't tested it. I just imaging it will happen like that. Thank you so much. - LeDuc

1 Answers

2
votes

Why not use SqlTransaction when performing the bulk insert. Yes your table will be locked for some time but atleast you'll guarantee data integrity.