sqlsql-servert-sqlidentity-insert

Set Identity_insert ON with start value

I work on SQL Server and I want to copy some rows from a tableA to tableB with MyColumnID included.

So, on the new table I want (before the copy) to Set Identity_insert tableB ON and in the end to Set Identity_insert tableB OFF with IDENTITY(500,1).

Below is my code: 

set IDENTITY_INSERT tableB ON
...code...
set IDENTITY_INSERT tableB OFF **with IDENTITY(500,1)**

I know that the last row is incorect. Can someone help me?

Solution

  • Changing the identity seed for the table Employees to 1000:

    DBCC checkident ('Employees', reseed, 1000)

    The next row inserted will begin at 1001.