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
Addressbut do not touch other columns)
Can anyone help me with that script? Any example would suffice. Thanks a lot
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)
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