When generating database scripts, I'm scripting data to be migrated to a different environment. Is there a setting in the script generation that I can enable to set IDENTITY_INSERT on/off automatically so I don't have to go through each table manually in the generated script and set it? I'm using SSMS and I'd like to do this via SSMS.
Here's what I am getting:
INSERT my_table (my_table_id, my_table_name) VALUES (1, 'val1');
INSERT my_table (my_table_id, my_table_name) VALUES (2, 'val2');
Here's what I want:
SET IDENTITY_INSERT my_table ON
INSERT my_table (my_table_id, my_table_name) VALUES (1, 'val1');
INSERT my_table (my_table_id, my_table_name) VALUES (2, 'val2');
SET IDENTITY_INSERT my_table OFF
You didn't say what tool you are using to generate your scripts, but if you have a tool like Red-Gate Data Compare, it will generate these statements for you, if you include the auto-increment field in the comparison. I'm not aware that SSMS has any such option.
If you have Visual Studio Premium or Ultimate edition, then you also have access to DBPro (Database Professional) that has a data compare and synchronization option. I believe this will generate the IDENTITY_INSERT statements for you as well.