Copy autoincremented datatable

Question

I have at my test database datatable Tags.

ID int autoincrement
Name nvarchar(255)

I want to transfer this table to my production database.

How to do it if I want to have the same ids foreach element

The problem is that my start index at testdatable is 15, and some ids don't exist because I removed theirs.

So:

Situation at Test datable

15 sport
18 money
30 homework

Desired situation at production datatable

15 sport
18 money
30 homework

At start production datatable is empty, and has autoincrement too.

Answer 1

Use the identity_insert feature on the destination table before inserting the data into the new table.

SET IDENTITY_INSERT YourNewTable ON

INSERT YourNewTable (ID, Name) SELECT ID,Name FROM YourOldTable

SET IDENTITY_INSERT YourNewTable OFF

Answer 2

You can turn the constraints off for the auto-incrementing field, load your data into the table, and then turn the auto-incrementing field back on.

Answer 3

you can use

SET IDENTITY_INSERT MY_TABLE ON
INSERT MY_TABLE (Id, Field1) VALUES (15, 'sport')
SET IDENTITY_INSERT MY_TABLE OFF

or make a backup/restore if the prod database is empty as you said (assuming it's the same database)