Database-wide equivalent of SET IDENTITY_INSERT OFF

Question

In my eternal saga to insert 1.4 million rows of data from a SQL script, I've written a basic WinForms app that takes each line of the script and executes it individually.

However, because the original script contained

SET IDENTITY_INSERT [Table] OFF

and SET is a session-wide command, this setting is getting lost on every SQL call, meaning that each line is failing. Is there a way to set IDENTITY_INSERT off for the whole table, database-wide just so I can make these individual calls without them failing? Or perhaps I can tell it to ignore the identity specification by appending a command to each line?

Answer 1

BULK INSERT won't work for you? Or the SQL Server Import/Export Wizard (here or here)? I know import/export can turn off identity insert for the whole import. I'm reasonably certain you can do it just prior to BULK INSERT

Answer 2

I believe that IDENTITY INSERT can only be overridden one table at a time, per session.

You will have to maybe batch up 2 or 300 insert statements at a time and precede each batch with the ident insert.

so the app will execute a block like this....

SET IDENTITY_INSERT [Table] OFF;
INSERT INTO TABLE VALUES (1, 'a');
INSERT INTO TABLE VALUES (2, 'b');
INSERT INTO TABLE VALUES (3, 'c');
INSERT INTO TABLE VALUES (4, 'd');
INSERT INTO TABLE VALUES (5, 'e');
SET IDENTITY_INSERT [Table] ON;

Answer 3

Is anything else going to be accessing/inserting to the table while this is 1.4 million insert job is going on.

If not, you could simply Disable the Identity on the PK column for the duration of the job?