Merging Partial Databases

Occasionally, you may find yourself in a situation where you have a partial database that you need to merge into another.

This script will copy all records from a Source database into a destination database.  If a record in the source database has the same Id as a record in the destination, that record will be skipped.

The following script 

Replace the Database Names

In the following script, set the @SOURCE_DB and @DEST_DB variables to the names of their respective databases.

If you want to copy the Result_Ids for all tables, simply use the query below.

DECLARE @SOURCE_DB NVARCHAR(MAX) = 'SOURCE_DATABASE_NAME'
DECLARE @DEST_DB NVARCHAR(MAX) = 'DEST_DATABASE_NAME'

DECLARE @SCRIPT NVARCHAR(MAX) = '

DECLARE @ColumnList NVARCHAR(MAX) = (
	SELECT
		STRING_AGG(QuoteName(COLUMN_NAME), '', '')
	FROM
		(
			SELECT TOP 99999
				COLUMN_NAME = CONVERT(NVARCHAR(MAX), Source.COLUMN_NAME)
			FROM
				[SOURCE_DB].INFORMATION_SCHEMA.COLUMNS Source, 
				[DEST_DB].INFORMATION_SCHEMA.COLUMNS Dest
			WHERE 1=1
				AND CONCAT(QUOTENAME(Source.Table_Schema), ''.'', QUOTENAME(Source.Table_Name)) = ''?''
				AND Source.TABLE_NAME = Dest.TABLE_NAME
				AND Source.COLUMN_NAME = Dest.COLUMN_NAME
				AND Source.COLUMN_NAME != ''Row''
			ORDER BY
				Source.ORDINAL_POSITION ASC
		) COLUMN_LIST
)

DECLARE @QUERY NVARCHAR(MAX) = CONCAT(
	''INSERT INTO [DEST_DB].? ('', @ColumnList, '') '',
	''SELECT '', @ColumnList, '' FROM [SOURCE_DB].? '',
	''WHERE Id NOT IN (SELECT Id FROM [DEST_DB].?)''
)

EXEC sp_executesql @QUERY


'

SET @SCRIPT = REPLACE(@SCRIPT, '[SOURCE_DB]', CONCAT('[', @SOURCE_DB, ']'))
SET @SCRIPT = REPLACE(@SCRIPT, '[DEST_DB]', CONCAT('[', @DEST_DB, ']'))


EXEC sp_MSforeachtable @SCRIPT

• Related Articles

• Merging Data into an Existing Application

  This is a highly technical process Experience with SQL queries is necessary to perform Join Migrations. Sometimes you may have customers who have data that needs to be restored into an existing system that is already set up and active. This type of a ...

• 02 - Working With Historic Users

  To Preserve or Merge? When a client moves into their new system, what do they want to happen with data tied to former employees? Do they want to keep it and attribute it to the original user? Do they want to attribute it to another user? Or do they ...

• 03.11 - Scoping Topics: Can you merge / clean up data?

  Data Sanitation Projects Suck Data sanitation projects tend to take a large number of hours and often results in unsatisfied customers. As a general rule, you should inform the prospect that you provide transportation - not sanitation - services for ...

• 03 - Working with Client-Provided CSV Data

  Sometimes firms will have a special CSVs that they may provide that will be used to tweak or adjust data in the backup. When you receive this data you should do the following: DO NOT import these CSVs directly into a Universal Migrator database. ...

• 02 - General Backup Guidelines

  When backing up data, the process will vary from one application to another, however, there are some general guidelines that you should always follow: Always Backup All Data You Will Ever Need When you are creating a backup, always back up all the ...