Generate MERGE scripts for all tables in a database


If you have been using the Database Projects (.sqlproj) in Visual Studio 2010 to maintain your database schema in version control, you probably need a good way to store static data for your application in version control as well. There are pre and post deployment scripts to handle that, but one of the cool things about SQL Server 2008 is that it allows for MERGE statements to perform the correct operation on the database side based on the state of the data in your target DB.

This let’s you specify what the static (also called seed, lookup table, and default) data needs to look like in a declarative way!

It’s very powerful, but often times by the time you get around to storing your database in version control, you already have tons of tables with tons of static data in there… and it would be a pain to hand-code all of the SQL MERGE statements.

That’s where a stored procedure called sp_generate_merge comes into play. It allows you to generate merge statements for a particular table.

However, I had an entire database full of tables that had static data… and didn’t feel like manually executing that stored procedure on the dozens of tables I had (I also wasn’t sure which ones had static data).

I came up with this quick-and-dirty SQL script to go through all of the tables in my database and generate the MERGE scripts for them. It’s still not perfect since you have to click on all of the links in the result set and copy/paste the content out into your own post-deployment script files.

But, better than doing everything manually:

USE {ENTER DATABASE NAME HERE}
GO

DECLARE @tname nvarchar(250), @tschema nvarchar(250)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.tables
ORDER BY TABLE_NAME
OPEN table_cursor;
FETCH NEXT FROM table_cursor;

-- Perform the first fetch
FETCH NEXT FROM table_cursor
INTO @tname, @tschema

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.sp_generate_merge @tname, @schema=@tschema

FETCH NEXT FROM table_cursor
INTO @tname, @tschema
END;
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO
Advertisements

About Bogdan Varlamov

A .NET Software Engineer that strongly believes technology should simplify and improve the quality of our lives instead of making them more complicated. View all posts by Bogdan Varlamov

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: