Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

Enjoy!

Total article views: 2673 | Views in the last 30 days: 238
 
Related Articles
FORUM

SQLCMD mode in Query Window using variables

Handling of variables in Query window while in SQLCMD mode

BLOG

Sqlcmd is dead. Long live Sqlcmd

SQL Server 2008 and higher ship with the invoke-sqlcmd cmdlet while SQL Server 2005 and higher inclu...

FORUM

SQLCMD not returning control

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

ARTICLE

Deploying Scripts with SQLCMD

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

FORUM

Batch Variable in sqlcmd

Batch Variable in sqlcmd

Tags
auto create sql    
automation    
data synchronization    
merge    
post-deploy    
sqlcmd mode    
ssdt    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones