Sync only new rows in a table

sbacheld

Hi,

We are in the process of automating database updates from our dev -> staging -> production process. One of the tables that we want to use sql data compare for has different values for each of the different environments. So it would look something like this:

dev: name=Row1, timeout=1
staging: name=Row1, timeout=10
production: name=Row1, timeout=15

Now, if we add a new row, we want that new row to get synced to all the databases, but would like any existing rows to remain unaffected. Is this possible with sql data compare? We are running this from the command-line if that makes a difference

Thanks,
Sean

Brian Donahue

Hi Sean,

Unfortunately I can't answer correctly without having the whole schema. SQL Data Compare matches rows based on the primary key or column(s) you choose to use as a row identifier. And it's not state-aware so it can't tell you if there are "new" rows. What you could possibly try is to use a WHERE clause on the tables and views tab and put a selection criteria there -- for instance if your table has a datetime column in it you could sync only records that are newer than 30 days by putting a WHERE clause in to compare only date > GETDATE()-30.

sbacheld

Thanks, Brian. We'll investigate a solution along those lines!

wdhenrik

If "new" rows is defined as rows that don't exist in the other table, you can certainly do this with SQL Data Compare.

Your comparison results are broken down into rows that exist only in the left table (To Insert), rows in both (To update) and rows existing only in the right table (To Delete).

If your new rows are in the left table, only select the records that show up in that section and create your deployment script. You should end up with only insert statements in your deployment script.

I haven't used the command line for this, so I'll have to defer back to RedGate if the command line support the same options the GUI does.

Hope that helps.