SQL Data Compare (SDC) is a great way to sync data among tables. It’s a software utility analogous to SQL Compare, but working with data rather than schema. I had a customer ask recently about setting up a SDC project and then calling that from the command line rather than using the GUI and clicking.
This post looks at how you can call a project from the command line. The project has a WHERE clause in it, so it uses the settings from the project.
We have the data shown here, from two different databases. There is 1 row in the first table that is not in the second table (in the second database).
I’ll build a SQL Data Compare project. In this project, I point to these two databases and the tables.
If I edit the project, I can choose the tables and views tab. Here I see my tables, and I select the dbo.RSSFeeds table.
When I select the row with dbo.RSSFeeds, I can then click the “Where clause” option and get a dialog where I can filter data. Here I can enter the where clause I used in the first query above. I also have the”use the same WHERE Clause” box checked.
Now I can save that project. I’ll then execute this from the command line. Note that I don’t have the SQL Data Compare install in my path, so I qualify both of these files, the executable and the project file. The call for me is:
"C:Program Files (x86)Red GateSQL Data Compare 14"sqldatacompare /project:"C:Usersway0uOneDriveDocumentsSQL Data CompareSharedProjects"DLM_Demo_RSS.sdc
You can see this being run below:
I can see there is a single row in the DB! that needs to move to DB2, which is the result I saw in the first queries above and in the SQL Data Compare gui.
If I add the /synchronize option to this call, SQL Data Compare will deploy the changes. Once I do that, I can query the two tables and see the data is the same. At least the data matching the WHERE clause.
Some of this is documented, but not worked through in an example, so I wrote this post to help myself and anyone else looking to work with SQL Data Compare from the command line. This is a great way to sync data easily between systems, if you have a repeatable set of data that you need to move.
SQL Data Compare is a very handy tool for checking and moving data between tables that needs to be synched. All sorts of lookup or reference data can be managed with SQL Data Compare. If you haven’t tried it, grab an evaluation and give it a try.
Disclosure: I work as an advocate for Redgate Software.