Let’s say you want to quickly provide a script to someone that will populate a particular table with data from your database. What if you have 200 tables? This could take some time unless you're using Red Gate's SQL Data Compare, but what if you have no access to the target database? You could certainly use the MERGE statement, but that’s a lot of code to write. What if you could run a script that created a SQL file containing all of the MERGE statements in one file and ready to run, after review of course.
Well, here's that alternative and hopefully not just something that will help you, but get you thinking about other possibilities with script automation and SQLCMD mode.
SQLCMD mode can be enabled via the SSMS Menu Query > SQLCMDMode or Tools > Options > Query Execution and checking the "By default, open new queries in SQLCMD Mode.
Besides, what self-respecting DBA doesn’t want to know how to do it via SQL?
What data is our best candidate? Well, just about any type, lookup, setup, or static data, etc...
We use an extended property called "SupportingData" to identify these tables. This script assumes that you have an EP on all of your supporting data tables, but this can be modified to fit your specific selection criterion.
This script does NOT handle all situations and I'm not advertising it as a complete script. i.e. data types like Text, NTEXT, XML, Image along with those pesky FK dependencies are not addressed. I’m sure there’s a way, it’s just not in this version.
There are many possible applications here, including:
As this was originally designed to be used with SSDT as a method to get our supporting data into source control. Utilizing a small home built VB app to parse the output file into individual files then including these files in your solution and setting the appropriate build action (Pre/Post deploy). POOF! Your data is now in Source Control !
You can put this into a stored procedure or function to call by a single table name, but you'll have to remove the SQLCMD parts. This would be beneficial if you need MERGES throughout the day during development.
*** This is provided as-is and with any code from the internet TEST, TEST, TEST! The goal in providing this script is to get the majority of the work done for you and for you to tweak as needed.
If you copy he output to a new query window the formatting should be retained.
Comments, suggestions and improvements are always welcome.