Script to migrate data between two SQL Server databases

Question

I have two SQL Server databases, and I need to write a script to migrate data from database A to database B. Both databases have the same schema.

I must loop through the tables and for each table I must follow those rules:

If the item I'm migrating does not exist in the target table (for example, the comparison is made on a column Name) then I insert it directly.
If the item I'm migrating exists in the target table then I need to only update certain columns (for example, only update Age and Address but do not touch other columns)

Can anyone help me with that script? Any example would suffice. Thanks a lot

EDIT:

I just need an example for one table. No need to loop, I can handle each table separately (because each table has its own comparison column and update columns)

buckley · Accepted Answer · 2012-05-24 12:36:04Z

8

The MERGE statement looks like it can help you here. An Example:

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);

The merge statement is available as of SQL Server 2008 so you are in luck

answered May 24, 2012 at 12:36

buckley

14.2k3 gold badges58 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Y2theZ Over a year ago

What if the Tables are in different databases?

buckley Over a year ago

Hi Youssef. Merging across different databases is supported, just use the fully qualified database name. More info: mikebosch.net/2010/08/…

Internet Engineer · Accepted Answer · 2012-05-24 12:39:50Z

0

Instead of creating a script why don't you copy the source table under a different name into the target server (update needs to take place).

Then just do a simple insert where name does not exist.

Here is the SQL for step 1 only.

INSERT INTO [TableA]
SELECT Name,
       XX,
       XXXX
FROM   TableB
WHERE  NOT NAME IN(SELECT NAME
                   FROM   TableA)

answered May 24, 2012 at 12:39

Internet Engineer

2,54410 gold badges41 silver badges54 bronze badges

Collectives™ on Stack Overflow

Script to migrate data between two SQL Server databases

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related