How to Dynamically Copy SQL Server Data Based on Matching Tables and Columns

By /

Migrating 20 Years of Data: How I Upgraded a Legacy Application Safely

Upgrading applications is always challenging, but upgrading one that’s 20 years behind the current version? That’s a whole new level of complexity. When I faced this task, it wasn’t just about installing a new version — it was about preserving decades of historical data while avoiding data corruption or downtime.

In this blog, I’ll walk you through how I approached dynamic, cross-database data migration for an old-to-new upgrade, tackling schema differences, identity columns, and computed fields along the way.

The Challenge: Bridging Two Decades of Data

The main problem was simple in theory but tricky in practice:

  • The old application database, OLDHEATSM, had tables and data structures that had evolved over 20 years.
  • The new application database, HEATSM, had updated schemas — some columns were renamed, some tables were added, and computed fields were introduced.
  • My goal: move as much data as possible without manually mapping every column, while avoiding broken keys, duplicates, or mismatches.

Simply running a bulk insert would have failed due to:

  • Identity columns (RecId)
  • Computed columns
  • Columns that existed in one DB but not the other
  • Collation differences

This required a smart, dynamic solution.

The Approach: Migrating Only Similar Tables and Columns

Instead of a brute-force copy, I decided to migrate only tables and columns that were similar across the old and new databases.

Here’s how the solution works:

1. Loop Through Matching Tables

I started by identifying tables that exist in both databases:

SELECT s.name
FROM   [OLDHEATSM].sys.tables s
JOIN   [HEATSM].sys.tables t
       ON s.name = t.name COLLATE DATABASE_DEFAULT

This ensures we only attempt to migrate tables that exist in both environments.

2. Find Columns That Can Be Safely Copied

Next, I dynamically checked which columns were compatible:

  • Excluded computed columns (can’t insert into them)
  • Skipped XML column sets
  • Identified identity columns so we could handle them properly

This is done using a temporary manifest table that lists common, insertable columns.

3. Build a Dynamic Insert Statement

Instead of hardcoding column names, the script builds the insert statement on the fly:

SELECT @ColumnList = STRING_AGG(QUOTENAME(ColName), ', ')
FROM #CommonCols;

This ensures that the migration automatically adapts to schema differences.

4. Handle Identity Columns

If a table has identity columns (like RecId), we toggle IDENTITY_INSERT:

SET IDENTITY_INSERT [HEATSM].dbo.[TableName] ON;
-- Insert data here
SET IDENTITY_INSERT [HEATSM].dbo.[TableName] OFF;

This prevents key collisions and maintains historical IDs.

5. Insert Only Missing Records

We don’t want duplicates, so the insert statement includes a NOT EXISTS check:

WHERE NOT EXISTS (
    SELECT 1
    FROM [HEATSM].dbo.[TableName] AS tgt
    WHERE tgt.RecId = src.RecId
);

The Full Script: 

DECLARE @TableName SYSNAME;DECLARE table_cursor CURSOR FOR
SELECT s.name
FROM [OLDHEATSM].sys.tables s
JOIN [HEATSM].sys.tables t
    ON s.name = t.name COLLATE DATABASE_DEFAULT
WHERE s.name = 'Problem';
--ORDER BY s.nameOPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @TableName;WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Processing table: ' + @TableName;    DECLARE @SourceDB SYSNAME = 'OLDHEATSM';
    DECLARE @TargetDB SYSNAME = 'HEATSM';
    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX);    -- 1. Create a Temp Table for column manifest
    IF OBJECT_ID('tempdb..#CommonCols') IS NOT NULL DROP TABLE #CommonCols;
    CREATE TABLE #CommonCols (
        ColName SYSNAME,
        IsId BIT
    );    -- 2. Dynamic SQL to find columns that exist in BOTH and are insertable
    DECLARE @GetColsSQL NVARCHAR(MAX) = '
    INSERT INTO #CommonCols (ColName, IsId)
    SELECT
        s.name,
        t.is_identity
    FROM ' + QUOTENAME(@SourceDB) + '.sys.columns s
    JOIN ' + QUOTENAME(@SourceDB) + '.sys.tables st ON s.object_id = st.object_id
    JOIN ' + QUOTENAME(@TargetDB) + '.sys.tables tt ON tt.name = st.name COLLATE DATABASE_DEFAULT
    JOIN ' + QUOTENAME(@TargetDB) + '.sys.columns t ON t.object_id = tt.object_id
        AND t.name COLLATE DATABASE_DEFAULT = s.name COLLATE DATABASE_DEFAULT
    WHERE st.name = @Table
      AND s.is_computed = 0    -- Cannot insert into computed columns
      AND t.is_computed = 0
      AND t.is_column_set = 0; -- Exclude XML column sets
    ';    EXEC sp_executesql @GetColsSQL, N'@Table SYSNAME', @Table = @TableName;    -- 3. Build the CSV list for INSERT and SELECT
    SELECT @ColumnList = STRING_AGG(QUOTENAME(ColName), ', ')
    FROM #CommonCols;    -- 4. Check for Identity columns to handle SET IDENTITY_INSERT
    DECLARE @HasIdentity BIT = 0;
    IF EXISTS (SELECT 1 FROM #CommonCols WHERE IsId = 1) 
        SET @HasIdentity = 1;    -- Safety Check
    IF @ColumnList IS NULL
    BEGIN
        RAISERROR('No matching columns found between the two tables.', 16, 1);
        RETURN;
    END    -- 5. Construct final SQL
    SET @SQL =
        CASE WHEN @HasIdentity = 1 
            THEN 'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + '.dbo.' + QUOTENAME(@TableName) + ' ON; '
            ELSE '' 
        END +
        'INSERT INTO ' + QUOTENAME(@TargetDB) + '.dbo.' + QUOTENAME(@TableName) + ' (' + @ColumnList + ') ' +
        'SELECT ' + @ColumnList + ' ' +
        'FROM ' + QUOTENAME(@SourceDB) + '.dbo.' + QUOTENAME(@TableName) + ' AS src ' +
        'WHERE NOT EXISTS ( ' +
        '    SELECT 1 FROM ' + QUOTENAME(@TargetDB) + '.dbo.' + QUOTENAME(@TableName) + ' AS tgt ' +
        '    WHERE tgt.RecId COLLATE DATABASE_DEFAULT = src.RecId COLLATE DATABASE_DEFAULT ' +
        '); ' +
        CASE WHEN @HasIdentity = 1 
            THEN 'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + '.dbo.' + QUOTENAME(@TableName) + ' OFF;'
            ELSE '' 
        END;    PRINT '--- GENERATED SQL ---';
    PRINT @SQL;    EXEC sp_executesql @SQL;    FETCH NEXT FROM table_cursor INTO @TableName;
ENDCLOSE table_cursor;
DEALLOCATE table_cursor;

Why This Method Works

  • Dynamic: Automatically adapts to matching tables and columns.
  • Safe: Skips computed fields and handles identity columns correctly.
  • Duplicate-Proof: Inserts only missing rows.
  • Collation-Aware: Prevents collation conflicts across databases.

Lessons Learned

  1. Automation is key — Manually mapping hundreds of tables and columns would have been impossible.
  2. Test, test, test — Always verify on a staging environment first.
  3. Don’t underestimate legacy complexity — 20 years of schema changes introduce subtle issues.
  4. Logging helps — Printing or logging generated SQL makes debugging easier.

Conclusion

Upgrading a 20-year-old application is as much about data preservation as it is about new features. By dynamically migrating only compatible tables and columns, I was able to bring decades of historical data safely into the new application, saving time and avoiding costly mistakes.

If you’re dealing with legacy systems, consider a schema-aware, dynamic migration approach — it might just save you weeks of manual work.

Related Posts

Scroll to Top