Command Line data script generation
DDaunais
Posts: 2 Bronze 1
Hello, I am trying to generate a data synchronization script between a database and a scripts folder (the scripts are generated by SQL Source Control). We use SQL Source Control, SQL Compare, and SQL Data Compare as tools. We source control database objects and some static data using SQL Source Control into TFS. We use SQL Compare and SQL Data Compare to generate deployment scripts in a continuous integration manner. This part seems to be working fine.
We also want to use these tools to generate reasonable rollback scripts. For database objects this is easy enough. For data rollback scripts, it is a little bit more challenging.
The database may have tables a, b, and c. The scripts folder may only contain data for table b. (but has the definitions for all tables).
When running SQLDataCompare from the command line, is there a way to generate data scripts for only tables that match between the two? When I try this now, an entire series of INSERT statements is generated for tables a and c, while the appropriate delta is generated for table b.
The sample syntax that I'm using looks something like this:
sqldatacompare.exe /server1:server1 /database1:"db1" /scr2:"c:\folder" /scriptfile:"c:\foo.sql" /include:identical
Thanks in advance for any pointers!
We also want to use these tools to generate reasonable rollback scripts. For database objects this is easy enough. For data rollback scripts, it is a little bit more challenging.
The database may have tables a, b, and c. The scripts folder may only contain data for table b. (but has the definitions for all tables).
When running SQLDataCompare from the command line, is there a way to generate data scripts for only tables that match between the two? When I try this now, an entire series of INSERT statements is generated for tables a and c, while the appropriate delta is generated for table b.
The sample syntax that I'm using looks something like this:
sqldatacompare.exe /server1:server1 /database1:"db1" /scr2:"c:\folder" /scriptfile:"c:\foo.sql" /include:identical
Thanks in advance for any pointers!
Comments
Unfortunately, this doesn't apply the other way around; so if you have the DB as the source and the scripts as the target, it'll still check all tables (because you may well want to add data from tables that don't currently have any in the scripts)
You can include / exclude records based on whether they are only on one side of the comparison or not, by using the additional / missing options along with /include or /exclude - does that help at all?
(You can see about those options in the commandline switches: http://www.red-gate.com/supportcenter/C ... 419878.htm)
Redgate Software