CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldata-transfer
SOLDIER-OF-FORTUNE
SOLDIER-OF-FORTUNE

Reputation: 1654

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?

Upvotes: 2

Views: 1844

Answers (4)

Diego
Diego

Reputation: 36176

try this:

delete from test1_db..query_string_interpretation

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

if you have an identity field you may have to write the name of the columns (except for the identity one). if you need to keep the IDs, take a look at the SET IDENTITY_INSERT ON statement here

Upvotes: 0

Alex_L
Alex_L

Reputation: 2654

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.

Upvotes: 1

Barry Kaye
Barry Kaye

Reputation: 7759

You can use a SQL wizard to do this for you. In Management Studio, right click on either database, select Tasks then Import Data... or Export Data...

Upvotes: 1

EBarr
EBarr

Reputation: 12026

Without more information on your part...generally you can use INSERT INTO/SELECT FROM to move data between tables.

The syntax is roughly:

INSERT INTO
test1_db..query_string_interpretation ( <your columns here>)
select <your columns here>
FROM test2_db..query_string_interpretation

Upvotes: 2

Related Questions