Today I’ll show you a special script which will let you “take a snapshot” of your table’s data, and apply it on an identical table somewhere else, or on the same table in a later point in time.
This script uses two useful concept which, when combined, can give you a really nice solution for generating a “snapshot” for your table data. These two concepts being MERGE and Table VALUES Constructor.
IMPORTANT NOTE: These two features are only available in SQL Server 2008 and newer.
I won’t go into detail on each of them because there’s enough material on MSDN’s Books Online:
But I will show you an example of how such a combination looks like to give you an idea on the concept (this is taken from the Table Values Constructor article):
Transact-SQL
USE AdventureWorks2012; GO -- Create a temporary table variable to hold the output actions. DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20)); MERGE INTO Sales.SalesReason AS Target USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) AS Source (NewName, NewReasonType) ON Target.Name = Source.NewName WHEN MATCHED THEN UPDATE SET ReasonType = Source.NewReasonType WHEN NOT MATCHED BY TARGET THEN INSERT (Name, ReasonType) VALUES (NewName, NewReasonType) OUTPUT $action INTO @SummaryOfChanges; -- Query the results of the table variable. SELECT Change, COUNT(*) AS CountPerChange FROM @SummaryOfChanges GROUP BY Change;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE AdventureWorks2012;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;
-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;
As you can see, the MERGE statement uses a set of hard-coded values as its source, instead of a table or a query.
Now imagine being able to automatically generate such a statement where the VALUES clause will contain all the rows in some table. And when you execute that statement, it will “reset” the table to match the content of the VALUES clause (e.g. INSERT new rows, UPDATE existing rows, and DELETE rows that don’t appear in the source).
This method could be used for two main purposes:
Synchronize the contents of a table in one location with an identically-structured table in another location.
Save a “snapshot” of a table’s data before a series of tests that manipulate the data on that table, and at the end of the tests – “reset” the table’s data to its initial state.
At the bottom of this article, you’ll be able to download 2 different scripts that work together:
usp_Generate_Merge_For_Table.sql
A stored procedure which receives a table’s name as parameter and outputs a MERGE statement containing all its current values.
Generate MERGE Statements for all tables.sql
A script which uses a cursor to loop through all tables in the database, and use BCP to execute the above procedure for each table, and save the output in a separate file.
Several important notes you must consider:
As mentioned before, these scripts will only work in SQL Server version 2008 or higher.
Since the MERGE statement needs some way to know how to check for existing values (to decide whether to do an INSERT/UPDATE/DELETE), only tables with a primary key are supported.
The second script which executes the procedure per each table uses a hierarchical query to generate the scripts in correct order of foreign-key relations, but if a DELETE is performed on a row referenced by a foreign key, the statement will work only if ON DELETE CASCADE is turned on. Otherwise, the statement will fail! Therefore, it’s highly recommended to set ON DELETE CASCADE ON on all the tables involved, unless you turn off the @delete_unmatched_rows parameter.
If your table has a column with the image data type, you may encounter some conversion errors. This has yet to be resolved.
I won’t go into detail about the provided scripts because I believe that reading the comments I wrote right next to the code is more helpful.
Here’s just a sneak peek of the stored procedure’s header so you can see the available configurations:
Transact-SQL
/* Sample usage: EXEC usp_Generate_Merge_For_Table 'Visitors', 'dbo' */ CREATE PROCEDURE usp_Generate_Merge_For_Table @CurrTable SYSNAME, -- table name @CurrSchema SYSNAME = 'dbo', -- table schema name @delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows @debug_mode BIT = 0, -- enable/disable debug mode @include_timestamp BIT = 0, -- include timestamp columns or not @ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns) @top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200) AS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*
Sample usage:
EXEC usp_Generate_Merge_For_Table 'Visitors', 'dbo'
*/
CREATE PROCEDURE usp_Generate_Merge_For_Table
@CurrTable SYSNAME, -- table name
@CurrSchema SYSNAME = 'dbo', -- table schema name
@delete_unmatched_rows BIT = 1, -- enable/disable DELETION of rows
@debug_mode BIT = 0, -- enable/disable debug mode
@include_timestamp BIT = 0, -- include timestamp columns or not
@ommit_computed_cols BIT = 1, -- ommit computed columns or not (in case target table doesn't have computed columns)
@top_clause NVARCHAR(4000) = N'TOP 100 PERCENT' -- you can use this to limit number of generate rows (e.g. TOP 200)
AS
And you can download the full scripts from here:
Make sure you review the code and read the comments in each of the scripts so you can understand what’s going on.
As always, please post any comments and questions you may have in the comments section below!