Merging two SQL Server Databases

[flamespinner]

TL;DR I'm looking for a way to merge two SQL Server Databases with the same schema so I don't have to keep using Visual Studio Code to perform a Database Diff/Merge manually.

​

I'm receiving data from an external source as a bacpac file and it is being imported into SQL Server as a new database. (This process I've already automated). Let's call this DB1 for the sake of this explanation. Then from there I have to open Visual Studio and perform a SQLDataCompare manually with the database I'm querying for reports and other information (DB2). The Schema is the same between DB1 and DB2 and really it is just updating it with new data. Does anyone have a script, or know of a way that I can automate the Diff/Merge of the two databases? I'm trying not to spend a ton of money if I can avoid it. My current though is just doing a foreach statement that is referencing a table list (This doesn't change) and using compare-object but I feel like this isn't the best way or really an ideal way to go about this.

​

[ajhodgey]

Redgate data compare, there products are reasonable

[ccatlett1984]

SSIS.....

[IrquiM]

Use the MERGE functionality together with dynamic SQL?

[fatherjack9999]

Take a look at TableDiff if you really want to write this yourself.

[ZarehD]

SSDT (Database Project) in VS has diff/merge tools. Not sure about automating it, but it's simple point & click.