Search code examples
sql-serversql-server-2008t-sqldata-transfer

SQL code to copy data from 1 database table to another

I have messed up the data. I have been ammended ids and values to the point where i cannot remember what the originals were. (testing a few things).

I have a table called query_string_interpretation and its part of the DB called, test1_db

I have a backup database which i have restored called, test2_db

How can I restore its contents from one database table to another?

Solution

  • At first, you need to be sure that you have all your data in source table, or data is placed in both tables - source and destination. In first case you need to truncate the destination table:

    TRUNCATE TABLE test1_db..query_string_interpretation

    Second, you need to be sure that you will insert the right values into IDENTITY fields, if these fields exists. Use SET INDENITY_INSERT ON statement. Third, you need to insert the values:

    insert into test1_db..query_string_interpretation
select * from test2_db..query_string_interpretation

    And don't forget to switch the INDENITY_INSERT to OFF, you you switched it to ON in the second step.