CODEWITHSUNDEEP
sql-server-2005t-sql
user278618
user278618

Reputation: 20252

Copy autoincremented datatable

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.

Upvotes: 1

Views: 67

Answers (3)

gsharp
gsharp

Reputation: 27927

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)

Upvotes: 1

George Johnston
George Johnston

Reputation: 32258

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.

Upvotes: 1

JohnFx
JohnFx

Reputation: 34909

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

Upvotes: 5

Related Questions