How to turn off identity for a database column of a table

Question

I have mistakenly deleted a table in one one my database server. I have same set of data in the other server.

What I have done is clicked on edit top 100 rows for the table, then copied it and pasted in the other server table.

But , here the problem is : one of the columns of the table is an identity. So, i was unable to copy that column here.

I need same identity values because these values are referred in the application, even if i copy the data and update it, new id values are being generated.

Is there any way to turn off identity for that column, paste these values and again turn on identity.

Answer 1

If I understand you correctly, try this one -

SET IDENTITY_INSERT <your_table> ON

INSERT INTO ... (ID, ...)

SET IDENTITY_INSERT <your_table> OFF

Answer 2

Use SET IDENTITY_INSERT as described here

Answer 3

you can use following query

Select * into TestNew from Test where ID < 100

Or you can try following

if you need to do this for only one table and only one time then do following.

Select and RIGHT CLICK ON YOUR DATABASE >> TASKS >> GENERATE SCRIPT >> a new window will open >> CLICK NEXT >> SELECT YOUR DB >> SET SCRIPT DATA TRUE in TABLE/VIEW OPTIONS >> CHOOSE TABLES >> SELECT YOUR TABLE >> SELECT SCRIPT TO NEW QUERY WINDOW

This will generate script of your source table

Just replace you new table name with your old table name