Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 5, 2013

Creating a Merge Script for your Reference Data

When you create a database project data script using the SQL Server Object Explorer, it provides a set of SQL Insert statements. So it requires that you delete all of the existing table data and insert the new rows. But what if you don’t want to delete all of the existing data? That is where a Merge script can help.

See this link for an introduction to SQL Server Object Explorer.

See this link for an introduction to the 2012 SQL Server Database Projects.

In this prior post, we used the SQL Server Object Explorer to generate a database project data script which looks like this:

image

But there are many cases where you don’t want to delete all of the existing data. Here are some examples:

  • You allow the user to change the display order, but not the Id or type name.
  • You allow the user to add new types, but not change the existing types.
  • You want to provide some initial data, but allow the user to change it in any way they want.

In these cases, executing a script that first deletes all of the rows and inserts all new one just won’t do. That is where a Merge script can help.

NOTE: To use a Merge script, your database must have its compatibility level set to a minimum of SQL Server 2008.

image

A MERGE script looks like this:

— Reference Data for the CustomerType table
SET IDENTITY_INSERT [CustomerType] ON
 
MERGE INTO [CustomerType] AS Target
USING (VALUES
            (1, N’Corporation’, 2),
            (2, N’Individual’, 1),
            (3, N’Educator’, 3),
            (4, N’Government’, 4))
AS Source ([CustomerTypeId],
            [TypeName],
            [DisplayOrder])
ON (Target.[CustomerTypeId] = Source.[CustomerTypeId])

WHEN MATCHED AND
            (Target.[TypeName] <> Source.[TypeName]) THEN
UPDATE SET
            [TypeName] = Source.[TypeName],
            [DisplayOrder] = Source.[DisplayOrder]

WHEN NOT MATCHED BY TARGET THEN
INSERT([CustomerTypeId],
            [TypeName],
            [DisplayOrder])
VALUES(Source.[CustomerTypeId],
        Source.[TypeName],
        Source.[DisplayOrder])

— delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
 
GO
 
SET IDENTITY_INSERT [CustomerType] OFF
GO

Let’s talk through what this script does:

  • MERGE INTO: Defines the target table name.
  • USING: Defines the default set of data.
  • AS Source: Defines the set of fields.
    • These must match the fields included in the default set of data.
    • They do not have to include all fields in the table.
  • ON: Defines how the Target (sample data) is to be compared to the Source (existing table data).
    • Normally this is matched up on the primary key.
  • WHEN MATCHED: Defines the script to execute when there is a match.
    • Since the ON in this example defined the primary keys, there is a match when the primary keys match.
  • AND: You can optionally add criteria to the match.
    • In this example, it will only execute the script if there is a primary key match AND the type name does not match.
    • You can add any other clauses here.
  • UPDATE SET: Defines the script to execute when the "WHEN" clause is true.
    • In this case, it will update both the TypeName and the DisplayOrder.
  • WHEN NOT MATCHED BY TARGET: Defines the script to execute when there is no match on the target (sample data).
    • So if there is no existing table data that matches the sample data, the script is executed.
    • In this case, if there is no matching primary key in the existing table data, it inserts the values.
  • WHEN NOT MATCHED BY SOURCE: Defines the script to execute when there is no match on the source (existing table data).
    • So if there is no sample data that matches the existing table data, the script is executed.
    • In this case, any rows not in the sample data are deleted.
  • ;: The MERGE script must end in a semicolon (;)

If you want to allow the user to add rows, remove the "WHEN NOT MATCHED BY SOURCE". Be sure to retain the ending semicolon.

Use a Merge script any time you want more control over how your data script is processed.

Enjoy!

For more information on these and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs