Ignoring columns at Syncronisation

Brian DonahueBrian Donahue Posts: 6,590 New member
Hi Paul,

Thanks for your post. You can only ignore columns at compare-time, and
those settings filter through at synchronization time. You shouldn't be
allowed to ignore the primary key column, however, unless you choose another
unique index as a comparison key.

I'm a bit confused here because I don't understand how Data Compare
would mark a row as missing and create a situation where you'd wind up with
a constraint violation because of a duplicate value when Data Compare has
found that the value doesn't exist in the target database!

Regards,

Brian Donahue
Red Gate Technical Support

"Paul McNally" <[email protected]> wrote in message
news:[email protected]
> Is there a way to ignore a column at syncronisation?
>
> I'm using identity columns throughout my DB. The identity column is part
> of the primary key. The comparision is correctly detecting new rows in
> the source DB.
>
> The problem is that the synchronisation is inserting the identity values
> which already exist in the target database. Is there a way to tell it to
> ignore these columns when it synchronises. The INSERT should still work
> as the identity columns will default to the next number, and I won't get
> constraint violations from duplicate identity values.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Paul,

    Does it work the way that you want if you exclude the TableId column
    from the data comparison?

    Regards,

    Brian Donahue
    Red Gate Technical Support

    "Paul McNally" <[email protected]> wrote in message
    news:[email protected]
    > G'day Brian,
    >
    > What we are doing is upgrading a production database from development.
    > In our development database we have a lot of default data rows which are
    > indicated by setting a CompanyId column to -1. On upgrade we want to
    > replace any CompanyId = -1 rows with those from the development database.
    >
    > For example a table in our development DB may look like this...
    >
    > CompanyId TableId Data
    > (identity)
    > -1 1 Our data
    > -1 2 More Data
    > -1 3 Still more data
    >
    > And the table in the production database is...
    >
    > CompanyId TableId Data
    > (identity)
    > -1 1 Our data
    > 1 2 Our data
    > 2 3 Our data
    >
    > To update we delete any rows in the production DB with CompanyId = -1.
    > Then we do a data compare and it correctly picks up all the development
    > DB rows as being missing (the PK is CompanyId, TableId). But when we
    > sync the data the production DB looks like this...
    >
    > CompanyId TableId Data
    > (identity)
    > 1 2 Our data
    > 2 3 Our data
    > -1 1 Our data
    > -1 2 More Data
    > -1 3 Still more data
    >
    > This gives us a contraint violation as soon as we try to access it
    > because of the duplicate TableId values. What I would like is this...
    >
    > CompanyId TableId Data
    > (identity)
    > 1 2 Our data
    > 2 3 Our data
    > -1 4 Our data
    > -1 5 More Data
    > -1 6 Still more data
    >
    > Which I think would be the result if the INSERT Data Compare used to add
    > the new rows didn't include the TableId identity column.
    >
    > Regards,
    > Paul.
    >
    > > Hi Paul,
    > >
    > > Thanks for your post. You can only ignore columns at compare-time,
    and
    > > those settings filter through at synchronization time. You shouldn't be
    > > allowed to ignore the primary key column, however, unless you choose
    another
    > > unique index as a comparison key.
    > >
    > > I'm a bit confused here because I don't understand how Data Compare
    > > would mark a row as missing and create a situation where you'd wind up
    with
    > > a constraint violation because of a duplicate value when Data Compare
    has
    > > found that the value doesn't exist in the target database!
    > >
    > > Regards,
    > >
    > > Brian Donahue
    > > Red Gate Technical Support
    > >
    > > "Paul McNally" <[email protected]> wrote in message
    > > news:[email protected]
    > >
    > >>Is there a way to ignore a column at syncronisation?
    > >>
    > >>I'm using identity columns throughout my DB. The identity column is part
    > >>of the primary key. The comparision is correctly detecting new rows in
    > >>the source DB.
    > >>
    > >>The problem is that the synchronisation is inserting the identity values
    > >>which already exist in the target database. Is there a way to tell it to
    > >>ignore these columns when it synchronises. The INSERT should still work
    > >>as the identity columns will default to the next number, and I won't get
    > >>constraint violations from duplicate identity values.
    > >
    > >
    > >
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Paul,

    Okay, I think I've finally got the point. The problem is that there is
    an identity column on TableId and you want the identity value to increment
    rather than duplicating the TableId from the source database's table. The
    reason that this doesn't work is that Data Compare tries to make a
    carbon-copy of the table by setting IDENTITY_INSERT ON and 'forcing' the
    destination table to have the same row identifiers as the source. In this
    case, Data Compare isn't going to work because it's always going to set
    IDENTITY_INSERT ON and copy the primary key columns' values from the source
    to the destination.


    Kind Regards,

    Brian Donahue
    Red Gate Technical Support

    "Paul McNally" <[email protected]> wrote in message
    news:[email protected]
    > G'day Brian,
    >
    > No it doesn't work, because the PK is TableId and CompanyId, even
    > excluding the TableId column it detects that the 3 columns are missing
    > from the target DB. That is correct because we've removed the CompanyId
    > = -1 rows from the target DB, and we want it to insert them.
    >
    > The problem is in the synchronise SQL script it creates, which is
    > something like this...
    >
    > BEGIN TRANSACTION
    > SET IDENTITY_INSERT [dbo]. ON
    > INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
    > 4, 'Our data')
    > INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
    > 5, 'More data')
    > INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
    > 6, 'Even more data')
    > SET IDENTITY_INSERT [dbo]. OFF
    > COMMIT TRANSACTION
    >
    > Even though there are already rows in the table with TableId's of 4, 5
    > and 6, hence the constraint violation. What I want it to do is create
    > script like this...
    >
    > BEGIN TRANSACTION
    > INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'Our data')
    > INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'More data')
    > INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'Even more
    > data')
    > COMMIT TRANSACTION
    >
    > It will still work, because TableId is an identity field, SQL Server
    > will assign values that don't clash with the rows which are already there.
    >
    > Regards,
    > Paul.
    >
    > > Hi Paul,
    > >
    > > Does it work the way that you want if you exclude the TableId column
    > > from the data comparison?
    > >
    > > Regards,
    > >
    > > Brian Donahue
    > > Red Gate Technical Support
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Paul,

    It doesn't look likely -- for the simple reason that in order for Data
    Compare to do what it's supposed to, make tables data appear identical, it
    needs to preserve the identities of the source table.

    Regards,

    Brian Donahue
    Red Gate Technical Support


    "Paul McNally" <> wrote in message news:[email protected]
    > Brian,
    >
    > Should I assume from the lack of response that we're not going to get an
    > option to skip the identity fields when data compare does these inserts?
    >
    > Paul.
    >
    >
    > > Hi Paul,
    > >
    > > Okay, I think I've finally got the point. The problem is that there
    is
    > > an identity column on TableId and you want the identity value to
    increment
    > > rather than duplicating the TableId from the source database's table.
    The
    > > reason that this doesn't work is that Data Compare tries to make a
    > > carbon-copy of the table by setting IDENTITY_INSERT ON and 'forcing' the
    > > destination table to have the same row identifiers as the source. In
    this
    > > case, Data Compare isn't going to work because it's always going to set
    > > IDENTITY_INSERT ON and copy the primary key columns' values from the
    source
    > > to the destination.
    > >
    > >
    > > Kind Regards,
    > >
    > > Brian Donahue
    > > Red Gate Technical Support
This discussion has been closed.