6

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.

1
  • I have about 170 rows in that table
    – Avinash
    Sep 2, 2013 at 8:26

3 Answers 3

7

If I understand you correctly, try this one -

SET IDENTITY_INSERT <your_table> ON

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

SET IDENTITY_INSERT <your_table> OFF
2

Use SET IDENTITY_INSERT as described here

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.