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:

1. 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.
2. 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)

Answer 1

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