Identity column - when can synchronize

kipb7

SQL Data Compare says in version 8.1, in Tools / Project Options / Options / Mapping Behavior / Include identity columns: "You cannot synchronize a view if it includes an identity column."

What does this mean? I have synchronized tables with identity columns, sometimes preserving the identity and sometimes not (though I'm a bit confused about how to get it to go each way.)

Chris Auckland

Thanks for your post.

I think the reason you can't sync a view with an identity column, is because you can't SET IDENTITY INSERT ON when updating a view. It reality though, it's pretty unlikely that you will be able to sync a view anyway, because it will only work if a view only references a single table. If a view references more than one table you'll get an error about not being to update multiple base tables.

With regards to sync'ing identity columns, if you set the option to include identity columns, then SQL Data Compare will SET IDENTITY INSERT ON and then insert the identity's on the target exactly as they are on the source. If you don't include identity, then the script will probably allow the server to assign the identity value when inserted, or leave it alone if the row already exists.

You can probably see what's happening if you check the script before you synchronize.

I hope this helps.