Using SQL Data Compare from the Command Line with a Project

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

2023-01-11 10_12_23-SQLQuery3.sql - localhost.db1 (WAY0UTWESTHP_way0u (59))_ - Microsoft SQL Server

I’ll build a SQL Data Compare project. In this project, I point to these two databases and the tables.

2023-01-11 10_12_34-SQL Data Compare - C__Users_way0u_OneDrive_Documents_SQL Data Compare_SharedProj

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.

2023-01-11 10_24_46-DLM_Demo_RSS.sdc

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.

2023-01-11 10_12_50-DLM_Demo_RSS.sdc_

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 Gate\SQL Data Compare 14"\sqldatacompare /project:"C:\Users\way0u\OneDrive\Documents\SQL Data Compare\SharedProjects"\DLM_Demo_RSS.sdc

You can see this being run below:

2023-01-11 10_13_13-cmdI 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.

2023-01-11 10_13_41-SQLQuery3.sql - localhost.db1 (WAY0UTWESTHP_way0u (59))_ - Microsoft SQL Server

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.