Update Table data

mesosoup

Hello all,

We are currently testing SQL Source Control in ongoing effort to migrate our deployment process to DLM automation. In our testing we found that when we do an update to the data of a table and check it into source control, we see that the script being created drops the rows that were updated then reinserts them with the updated values. This is something that will not work in our environment since we have a lot of data that has foreign keys and such. Is there a setting or something that we are missing in source control that will allow for an update statement to be created instead of dropping the data and reinserting or is this just how source control works and we need to figure out a workaround for any data changes?

Thank you in advanced for your update.

Andrew P

Hey Mesosoup,

Thanks for contacting us!
I assume you are using the Static Data feature in SQL Source Control - the data scripts in Source Control always use Insert statements regardless of the change type - similarly the schema scripts only use Create scripts regardless of the schema change. This is because these scripts form a file-folder version of the database - SQL Compare and Data Compare will actually do the comparison between the scripts folder and the target database (they are at the heart of our DLM offering), and generate deployment scripts that will work well in the target database (eg Alters, Updates, etc).

If you use SQL Data Compare to compare the Source Control scripts folder to the database you wish to deploy those changes to, do you see it doing a delete/insert or does it do updates?

Kind Regards,

mesosoup

What i did was create a data script of the table as it is PRIOR to an update and checked that into source control. I then made a change to the data in the database and did a data compare against the script in source control and its doing an insert. Currently when we have updates to tables our developers create an update script we check that into tfs and deploy to production using SQLCMD. I was trying to see if this was something that could be automated using SQL Source control, DLM and compare.