This is a special script I wrote which will let you “take a snapshot” of your table’s data, and apply it on an identically structured table somewhere else, or on the same table in a later point in time.

This script uses two useful concepts 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 Microsoft Docs:

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):

 

SQL
Edit|Remove
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:

  1. Synchronize the contents of a table in one location with an identically-structured table in another location.
  2. 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.

In the downloadable file, you’ll find 2 different scripts that work together:

I won’t go into detail about the provided scripts because I believe that reading the comments that 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:

SQL
Edit|Remove
CREATE PROCEDURE [dbo].[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 
    @update_existing_rows    BIT = 1,    -- enable/disable UPDATE of rows 
    @insert_new_rows        BIT = 1,    -- enable/disable INSERT 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 (incase 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

Make sure you review the code and read the comments in each of the scripts so you can understand what’s going on.

And you can also download the full scripts from my GitHub Gist here:

https://gist.github.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb

Remarks