Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
you need to have "SET IDENTITY_INSERT Testtable ON" befor inserting data in your identity columns. how many rows are there in your source table ?? –  Dhaval Sep 2 '13 at 8:13
    
I have about 170 rows in that table –  Avinash Sep 2 '13 at 8:26
    
can you please see my answer below which might help you –  Dhaval Sep 2 '13 at 8:31
add comment

migrated from stackoverflow.com Sep 2 '13 at 11:03

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

If I understand you correctly, try this one -

SET IDENTITY_INSERT <your_table> ON

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

SET IDENTITY_INSERT <your_table> OFF
share|improve this answer
add comment

Use SET IDENTITY_INSERT as described here

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.