Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Jason Selburg,
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:
*** 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.
Handling of variables in Query window while in SQLCMD mode
SQL Server 2008 and higher ship with the invoke-sqlcmd cmdlet while SQL Server 2005 and higher inclu...
running scripts against remote server via sqlcmd control not returned to calling script
Deploying changes to your SQL Servers can be a challenge. Longtime SQL Server expert David Poole bri...
Batch Variable in sqlcmd