Inserting differences instead of updating them

Authorized_

Hello,

I'm writing a small tool that synchronizes the databases automatic between the empty master and the target, which is running at our clients, and contains important data.

The master database contains only some basic values.
Our clients can add and as many rows they want.

So, in some cases, data on the target may not be deleted or updated. Instead of the delete or update, the program needs to insert a new row.

But how do you do that in the sdk?

Thanks in advance,
Authorized_

Chris Auckland

You should be able to stop any updates or deletes using something like:

foreach (Difference difference in stagingVsProduction)
                {
                    if (difference.Type == DifferenceType.OnlyIn1)
                    {
                    difference.Selected = true;
                    } else
                    difference.Selected = false;                    
                }

This will mean that only new rows will be inserted on the target from the source.

I hope this helps.

Authorized_

Hi,

Thank you for your reply Chris.

I understand what you're trying to do. but the difference will not be executed by using this code. But I want the difference to be executed in an insert-statement instead of doing an update.

something like
if (difference.querytype == querytype.update)
{
difference.querytype = querytype.insert
}

Chris Auckland

Thanks for your reply.

If you're hoping to insert the rows as new rows rather than updating the existing rows, then unless you reassigned the PK for the new rows, you would probably encounter duplicate key violations when you ran the script. In other words, if the default is to update the row with ID 15, if you instead tried to insert a row with ID 15 then the script would fail as ID 15 already exists.

There isn't a way in SQL Data compare to reassign a primary key value, but you could try using a different comparison key in the project and also exclude the column you were using as a key before. If the PK column is excluded from the project, then SQL Server will assign a value when the row is inserted, but how this would match up with any other related rows is difficult to say.

It sounds like you're almost wanting to merge data, which is pretty difficult to do with SQL Data Compare, as it's primarily a synchronization tool.

Authorized_

Hi,

I am aware of the problems with the primary keys, references and stuff.
But that isn't a problem in our database.

Merging is in fact the correct word.
But I think I'm going to create a "SQLInsertStatementBuilder" and execute the statements through ADO.net.

It's maybe an idea for an next version of SQL Data Compare.