Script to transfer data between two SQL Server databases

Question

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

I have to scroll through the tables, and for each table I have to follow these rules:

• If the element that I am moving does not exist in the target table (for example, the comparison is done in the Name column), then I insert it directly.
• If the element that I am moving exists in the target table, I only need to update certain columns (for example, update only Age and Address , but not touch other columns)

Can someone help me with this script? Any example would be sufficient. Many thanks

EDIT:

I just need an example for one table. There is no need for a loop, I can process each table separately (since each table has its own comparison column and update columns)

Answer 1

Operation MERGE looks like it can help you. 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 SQL Server 2008, so you're in luck

Answer 2

Instead of creating a script, why don't you copy the source table under a different name to the target server (the update should happen).

Then just do a simple insertion where the name does not exist.

Here are just the SQL for step 1.

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