SQL set identity and retain values

Question

I have a problem restoring a table. I wanted to restore data by copying data from the backup to the table. The problem is, the PK of the table has the Identity-Property set. So when I inserted the lost rows they got new IDs. I created a new table without identity and put the data in there. Now I want to turn on Identity on the PK column, which doesn't work.

Any what I can do or if this is possible at all?

Use [`SET IDENTITY_INSERT`](https://msdn.microsoft.com/en-us/library/ms188059.aspx) — Vladimir Baranov, Mar 07 '17 at 10:56

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

You cannot add IDENTITY property to existing column. Either you insert in existing table with IDENTITY column with IDENTITY_INSERT ON' option or you create a newIDENTITY` column in the new table.

This post might help you

edited May 23 '17 at 11:46

Community

1
1

answered Mar 07 '17 at 12:20

Venu

455
2
7

score -1 · Answer 2 · edited May 23 '17 at 11:46

-1

This will solve your problem [https://stackoverflow.com/a/1049305/6652909]

edited May 23 '17 at 11:46

Community

1
1

answered Mar 07 '17 at 10:56

Mohammad

132
5

score -1 · Answer 3 · answered Mar 07 '17 at 10:58

-1

If you have backup of table then TRUNCATE your table then set primary key then make it auto increment in table and then copy your table and make sure your data of table is entered properly, it worked for me may be it will helpful to you also.

answered Mar 07 '17 at 10:58

Sweta Parmar

269
1
11

SQL set identity and retain values

3 Answers3