ADO.NET 3.5 Cookbook, 2nd Edition

6.10. Merging Data in Two Database Tables

Problem

You need to merge the data in two SQL Server 2008 database tables.

Solution

Use the T-SQL MERGE statement.

The solution needs a table named MergeTable in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE MergeTable(
	    ID int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution needs some sample data in the table MergeTable. Create the data by executing the following T-SQL batch:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO MergeTable VALUES (1, 'Field1.1', 'Field2.1')
	INSERT INTO MergeTable VALUES (2, 'Field1.2', 'Field2.2')
	INSERT INTO MergeTable VALUES (3, 'Field1.3', 'Field2.3')

The solution needs a second table named MergeTableDelta. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE MergeTableDelta(
	    ID int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution needs some sample data in the table MergeTableDelta. Create the data by executing the following T-SQL batch:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO MergeTableDelta VALUES (2, 'Field1.2', 'Field2.2 (new)')
	INSERT INTO MergeTableDelta VALUES (4, 'Field1.4', 'Field2.4')
	INSERT INTO MergeTableDelta VALUES (5, 'Field1.5', 'Field2.5')

The solution fills a DataTable with data from the destination merge table named MergeTable in the AdoDotNet35Cookbook database and output to the console. A second ...