What are the challenges you face when working across database platforms? Take the survey
Options

Inserting differences instead of updating them

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_

Comments

  • Options
    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.
    Chris
  • Options
    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
    }
  • Options
    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.
    Chris
  • Options
    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.
Sign In or Register to comment.