SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

Create MERGE statements with data!

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:

  1. 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 !   
  2. 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.


Total article views: 4947 | Views in the last 30 days: 2
Related Articles

SQLCMD mode in Query Window using variables

Handling of variables in Query window while in SQLCMD mode


SQLCMD not returning control

running scripts against remote server via sqlcmd control not returned to calling script


Avoiding confusion with SQLCMD variables

SQLCMD variables can be a useful way of having changeable parameters for your SQL scripts, allowing ...


Deploying Scripts with SQLCMD

Deploying changes to your SQL Servers can be a challenge. Longtime SQL Server expert David Poole bri...


Batch Variable in sqlcmd

Batch Variable in sqlcmd