CODEWITHSUNDEEP
sqlcopyssms
espresso_coffee
espresso_coffee

Reputation: 6110

How to copy table from one Data Base to another?

I want to copy some tables from my DB1 to my DB2. Tables in DB1 are same as tables on DB2 but data in the tables are different. I want to copy tables from DB1 to DB2 but to keep old tables and data on DB2. How I can do this with Microsoft SQL Server Management Studio? I tried to right click and do the export but before I have to click on Finish button looks like that will just copt all data from DB1 to DB2 and I do not want that. If anyone can help with this please let me know. Thank you.

Upvotes: 0

Views: 4544

Answers (4)

user6221192
user6221192

Reputation: 

USE db2;

CREATE TABLE table2 LIKE db1.table1;

INSERT INTO table2  
    SELECT * FROM db1.table1;

This is also a way to copy a table with its records to another database.

Upvotes: 0

blackbishop
blackbishop

Reputation: 32710

If you want to keep old data in the destination table (or juste update it), so you might use a Merge:

MERGE INTO db2.dbo.table1 B
USING (
  SELECT *
  FROM db1.dbo.table1) A
ON (A.Column1 = B.Column1 )
WHEN MATCHED THEN
  -- update
WHEN NOT MATCHED THEN
  -- insert new rows

Upvotes: 0

Paul Swetz
Paul Swetz

Reputation: 2254

So you want to merge the schema AND data from DB1 into DB2?

You should list out the exact requirements, the question is still vague even with that info. What data do you want to keep, what is ok to blow out? What schema do you want to keep, are you archiving the old tables? Changing table names?

If you are literally trying to merge db1 into db2 your issue is going to be in managing the relationship ids which will be getting reassigned since DB2 could already be using IDs that are present in DB1.

Upvotes: 0

DoubleT28
DoubleT28

Reputation: 185

You can export the tables from DB1 with another name to DB2 if you don't want to modify them. In the export wizard just change the name of the destination table.

Upvotes: 1

Related Questions