Generate data inserts for non existing tables in target database

rarifiedone

Hi, I'm new to this toolset.  I like it a lot, but have a question.  Let me know if this should be cross posted to sql data compare or if the answer exists elsewhere.  I have not found it.  Thank you in advance for your help.

So we have two relevant tools to our situation.  One creates scripts that sync database schema, updates tables, creates new ones, etc.  The other (and the part I really like about redgate) is that it will create statements to update and insert (and remove?  haven't tried) missing data in tables between two databases.  

What I would like is an option to also generate data insert scripts for any table that does not exist in my target database when I do a schema compare.  I think it is a reasonable use case.  I have a table in database A that I want created in database B and because the data in the table in database A is more or less constant I want to port that data over as well.  I want to pre-populate the data for my created table in database B with whatever is in database A.  Now this is perfectly obtainable by generating my schema changes, committing them, then doing my data compare and generating scripts based on my two (now existing) tables, one which has data in database A, but not database B.  

Unfortunately, in my current environment, test backups of my target database are not always immediately available.  I can see it, connect to it and do schema and data compares, but I do not have execute permissions.  I lack the ability to complete the intermediary step of committing my schema changes so that the SQL data compare tool will see that I have data in a table in database A that is missing in the corresponding table in database B.  What I'm hoping, is that there is an option in the sql compare tool that will recognize that when we create a table, we often want to put our data with it.  I recognize that these two functions are usually separated, thus the two tools, but for the powerless amongst us who are merely generating scripts for other godlike sql administrators to execute, does this option exist?

Thanks!

DanC

Hi @rarifiedone so if I'm understanding your scenario correctly I have a few potential solutions for you!

So first one would be using SQL Source Control (SOC) to create a scripts folder using SQL Compare Pro CMD using the switch /Include:StaticData. So the way this would work is to set SOC to a working folder and setup tables to have static data, then in the Compare commandline use the SOC working folder as the source to compare against "database b".

The documentation here will take you through setting up different sources for comparison - https://documentation.red-gate.com/sc/setting-up-the-comparison/setting-data-sources.
 Also the documentation here it will show an example of using the StaticData switch - https://documentation.red-gate.com/sc/using-the-command-line/switches-used-in-the-command-line

This option would combine the functionality of both SQL Compare and Data Compare in one go. 

Second option, you would do the process in two stages just using SQL Compare and Data Compare. Essentially you will first use SQL Compare to compare database A to database B and generate a deployment script to deploy the schema and objects into SQL Server (which the godlike sql admins will use).

Second stage would once the deployments have occurred, you would then use Data Compare to compare A-B once again, then generate a deployment script for the data you wish to deploy. 

Appreciate that's a slightly slower and time consuming process for what you're trying to achieve! 

Hope this covers your query!