Set Identity_insert ON with start value

Question

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?

You've got the sense of `identity_insert` the wrong way around. When you set it to `ON`, you're saying "**I'm** going to insert values into the identity column", and when you set it to `OFF`, you're saying "go back to automatically assigning values to the identity column". It wouldn't make sense to tell it something about identity values to use during an `ON` call since the system's not going to be assigning any values. — Damien_The_Unbeliever, Mar 08 '18 at 12:33
I have check it, It won't. But i will use count+1 just for sequrity. Thank you! — S3minaki, Mar 08 '18 at 12:33

score 3 · Accepted Answer · answered Mar 08 '18 at 12:24

3

Changing the identity seed for the table Employees to 1000:

DBCC checkident ('Employees', reseed, 1000)

The next row inserted will begin at 1001.

answered Mar 08 '18 at 12:24

scar80

score 1 · Answer 2 · edited Mar 08 '18 at 13:25

1

Try this:

DBCC CHECKIDENT ('tableB ', RESEED, 500);

edited Mar 08 '18 at 13:25

Zohar Peled

answered Mar 08 '18 at 12:23

Stack Overflow

2 Answers2