Script to Copy Data Between Two SQL Server Databases

I recently had a requirement to "copy" the data between two identical database schemas. Actually the requirement was to "rebuild" a database from the ground up (objects and data). I almost always Visual Studio SQL Server projects (available after installing SSDT as well as with the latest versions of Visual Studio) when doing database development which means I can deploy an "empty" database with more or less the click of a mouse so I won't get into creating the actual database, only populating it. If you need to copy the database objects to a new database there are tools built within SQL Server Management Studio to allow you to do this. You can check out this TechNet post for more information. You can also generate DML or data population scripts from SSMS but it can result in massive files if you have a database with a decent amount of rows. My databases were rather large so I wanted to copy the data from one database to the other without having to explicitly write anything to disk.

This stored procedure handles tables with Identity columns as well as Computed columns. It also only attempts to populate tables that have zero rows. You might have different requirements so feel free to enhance it and post any enhancements that you think would be valuable to others.

CREATE PROCEDURE [dbo].[apUtilCopyDataFromDatabase] @SourceLinkedServerName     VARCHAR(128) = NULL,
                                       @SourceDatabaseName         VARCHAR(128),
                                       @SourceSchemaName           VARCHAR(128),
                                       @SourceTableNamePrefix      VARCHAR(128) = '',
                                       @TargetLinkedServerName     VARCHAR(128) = NULL,
                                       @TargetDatabaseName         VARCHAR(128) = NULL,
                                       @TargetSchemaName           VARCHAR(128) = 'dbo',
                                       @OnlyPopulateEmptyTablesInd BIT = 0
AS
  BEGIN
 SET NOCOUNT ON;
 DECLARE @SQLCursorData       NVARCHAR(MAX),
              @SQLTruncate         VARCHAR(MAX),
              @SQL                 VARCHAR(MAX),
              @TableName           SYSNAME,
              @SchemaName          VARCHAR(MAX),
              @HasIdentity         BIT,
              @CurrentDatabaseName VARCHAR(128) = DB_NAME(),
 @TargetDatabase VARCHAR(128) = @TargetDatabaseName; --ToDo: Cleanup confusing names  

      DECLARE @SQLNoCheck NVARCHAR(4000) = 'USE ' + @TargetDatabase + '; EXEC sp_MSforeachtable @command1 = ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''; USE ' + @CurrentDatabaseName + ';';
 EXEC sp_executesql @SQLNoCheck;

      SET @SourceDatabaseName = IIF(@SourceLinkedServerName IS NOT NULL, QUOTENAME(@SourceLinkedServerName) + '.', '')
                                + QUOTENAME(@SourceDatabaseName) + '.'
                                + QUOTENAME(@SourceSchemaName);

      SET @TargetDatabaseName = IIF(@TargetLinkedServerName IS NOT NULL, QUOTENAME(@TargetLinkedServerName) + '.', '')
                                + IIF(@TargetDatabaseName IS NOT NULL, QUOTENAME(@TargetDatabaseName) + '.', '')
                                + QUOTENAME(@TargetSchemaName)

      SET @SQLCursorData = 'DECLARE TableCursor CURSOR GLOBAL FOR
        SELECT
          s.NAME
          ,t.NAME
        FROM ' + @TargetDatabase + '.sys.tables t
          JOIN ' + @TargetDatabase + '.sys.schemas s
            ON t.schema_id = s.schema_id
          JOIN ' + @TargetDatabase + '.sys.objects AS o
            ON t.object_id = o.object_id
          JOIN ' + @TargetDatabase + '.sys.partitions AS p
            ON o.object_id = p.object_id
        WHERE
          t.type = ''U''
          AND t.NAME LIKE ''' + @SourceTableNamePrefix + '%''
        GROUP  BY
          s.NAME
          ,t.NAME
      HAVING SUM(p.rows) = 0 
OR ' + CAST(@OnlyPopulateEmptyTablesInd AS VARCHAR(1)) + ' = 0;'

      EXEC sp_executesql @SQLCursorData;

      OPEN TableCursor;

      FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

      DECLARE @ColumnList VARCHAR(MAX);

      WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
PRINT CHAR(13) + CHAR(10)
                      + '-----------------------'
                      + QUOTENAME(@SchemaName) + '.'
                      + QUOTENAME(@TableName)
                      + '-----------------------'

--ToDo: Add logic to determine if there are FK constraints. TRUNCATE if not, DELETE if there are.
                IF (@OnlyPopulateEmptyTablesInd = 0)
BEGIN
SET @SQLTruncate = 'DELETE FROM '
  + QUOTENAME(@TargetDatabase) + '.'
  + QUOTENAME(@SchemaName) + '.'
  + QUOTENAME(@TableName) + ';';

EXEC(@SQLTruncate);

PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' rows deleted.';
END

                DECLARE @SQLHasIdentity NVARCHAR(1000) = 
'USE ' + @TargetDatabase + '; 
SELECT @val = CAST(SUM(CAST(is_identity AS TINYINT)) AS BIT)
                        FROM ' + @TargetDatabase + '.sys.columns
                        WHERE object_id = OBJECT_ID(QUOTENAME(''' + @SchemaName + ''') + ''.'' + QUOTENAME(''' + @TableName + ''')); 
USE ' + @CurrentDatabaseName + ';';

                EXEC sp_executesql @SQLHasIdentity, N'@val bit OUTPUT', @val = @HasIdentity OUTPUT;

SET @ColumnList = NULL;

                DECLARE @SQLColumnList NVARCHAR(4000) = 
'USE ' + @TargetDatabase + '; 
DECLARE @ColList NVARCHAR(MAX);
SELECT @val = COALESCE(@val + '','', '''') + QUOTENAME(name)
FROM '
 + @TargetDatabase + '.sys.columns
WHERE
 object_id = OBJECT_ID(QUOTENAME(''' + @SchemaName + ''') + ''.'' + QUOTENAME(''' + @TableName + '''))
 AND is_computed = 0 
 AND NAME NOT IN ( ''CreatedDate'', ''CreatedUser'', ''LastUpdatedDate'', ''LastUpdatedUser'' ); 
 USE ' + @CurrentDatabaseName + ';';

                EXECUTE sp_executesql @SQLColumnList, N'@val VARCHAR(MAX) OUTPUT', @val = @ColumnList OUTPUT;                

                SET @SQL = IIF(@HasIdentity = 1, 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ON;', '')
                           + 'INSERT INTO ' + @TargetDatabaseName + '.'
                           + @TableName + ' (' + @ColumnList + ') SELECT '
                           + @ColumnList + ' FROM '
                           + CONVERT(VARCHAR, @SourceDatabaseName) + '.'
                           + QUOTENAME(@TableName) + ' '
                           + IIF(@HasIdentity = 1, 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' OFF ', '')
                           + ';';

                EXEC(@SQL);

PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' rows inserted.';
            END TRY
            BEGIN CATCH
                PRINT ERROR_MESSAGE();
            END CATCH

            FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;;
        END

      CLOSE TableCursor;

      DEALLOCATE TableCursor;

 DECLARE @SQLCheck NVARCHAR(4000) = 'USE ' + @TargetDatabase + '; EXEC sp_MSforeachtable @command1 = ''ALTER TABLE ? CHECK CONSTRAINT ALL''; USE ' + @CurrentDatabaseName + ';';
 EXEC sp_executesql @SQLCheck;

      RETURN 0
  END