Just a quick helper for anybody looking to create a table on Microsoft SQL Server, using TSQL. When setting up a table with an IDENTITY column, if you have the need to insert exisiting data into your new table, you must be familiar with the SET INDENTIY_INSERT [table] ON / OFF statement. One thing to note:
If you are working with multiple tables, only ONE table at a time can receive this statement. If you try and use this statement on multiple tables or across multiple tables at once, you will receive an error that looks like the following:
Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table Name.
Cannot perform SET operation for table Table Name.
An example for using this is shown below:
– Create myTable table.
CREATE TABLE myTable (id int IDENTITY PRIMARY KEY, mycol varchar(40))
GO
– Inserting values into myTable table.
INSERT INTO myTable (mycol) VALUES (‘answer 1′)
INSERT INTO myTable (mycol) VALUES (‘answer 2′)
INSERT INTO myTable (mycol) VALUES (‘answer 3′)
INSERT INTO myTable (mycol) VALUES (‘answer 4′)
GO
– Now Add Specific Value
SET IDENTITY_INSERT myTable ON
INSERT INTO myTable (id, mycol) VALUES(5, ‘answer 5′)
SET IDENTITY_INSERT myTable OFF
and that's it. Quite a simple SET statement but very handy.