SQL Server – Insert records with value in identity column

Hi guys,

Today I will give you a simple tip, more than a tip it is an script ready to run. You will be able to test an insert setting a value for a identity column on SQL Server.

This is a small example about how to insert rows into a table setting a value for a identity column on SQL Server. Basically the script will do the following:

• Create a temp table.
• Insert a row into the temp table without a specific value for the identity column.
• Enable the mode that we can give a value for the identity column.
• Insert two rows setting a value for the identity column.
• Disable the mode that we can give a value for the identity column.
• Insert a new row into the temp table without the value for the identity column. (We will do it again just to check that SQL Server will set the right value for our identity column).
• Drop the temp table.

This is the example:

CREATE TABLE #TempTable
(
	Id   INT IDENTITY, 
	Name VARCHAR(50)
)
--Verify the empty table
SELECT * FROM #TempTable
--Inserting the first row without identity
INSERT INTO #TempTable (Name) 
VALUES ('Thiago Satoshi Suzuki - 1')
--Vefify first record
SELECT * FROM #TempTable
--Set the Identity Insert to you inform the Id
SET IDENTITY_INSERT #TempTable ON
INSERT INTO #TempTable (Id, Name) 
VALUES (5, 'Thiago Satoshi Suzuki - 5')
INSERT INTO #TempTable (Id, Name) 
VALUES (8, 'Thiago Satoshi Suzuki - 8')
--Vefify 3 records
SELECT * FROM #TempTable
--Set the Identity Insert OFF to you do not inform the Id
SET IDENTITY_INSERT #TempTable OFF
--Insert new record without identity
INSERT INTO #TempTable (Name) 
VALUES ('Thiago Satoshi Suzuki - 9')
--verify all records
SELECT * FROM #TempTable
--Drop the temporary table
DROP TABLE #TempTable

This is what would happen if we try to set a value for a identity column without setting the IDENTITY_INSERT as ON.

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table ‘#TempTable__________________________________________________________________________________________________________000000001076’ when IDENTITY_INSERT is set to OFF.

Click on the following link to see more details about the identity_insert:
SET IDENTITY_INSERT

Thanks for reading.
Like and Share if you found it may be useful to someone you know!

Would you like to check the Portuguese version?
Clique on the Brazilian flag ->