[SQL Server] Quick Table Data Copy

[SQL Server only]

Purpose

Quickly copy data for 1 or more tables from one database to another.  This is useful for performing partial data refreshes between environments.

About

This query is optimized for speed.  For each table: disable all indexes, truncate table, copy data from source, rebuild indexes.

Usage

1. Specify table(s) to be refreshed in @tableList (“PARAMETER 1”)
2. Specify source database & schema in @sourcePrefix (“PARAMETER 2”)
3. Specify destination database & schema in @destPrefix (“PARAMTER 3”)
4. Running this query will output a new set of queries for you to review & execute on your own.

Warnings

• Destination table is completely overwritten.
• Uses TRUNCATE (transaction cannot be rolled back)
• Does not update table specs, will not work if table specs are out-of-sync.

DECLARE @tableList as TABLE (
    tableName NVARCHAR(255)
);

-- ** PARAMETER 1: Table List **
INSERT INTO @tableList VALUES 
    ('F0101'),
    ('F0301'),
    ('F0111'),
    ('F3701'),
    ('F40318'),
    ('F40072');

-- ** PARAMETER 2: Source environment (where will we copy data from?) ** --
DECLARE @sourcePrefix NVARCHAR(255) = 'JDE_PRODUCTION.PRODDTA';

-- ** PARAMETER 3: Destination environment **WARNING! TABLE CONTENTS WILL BE OVERWRITTEN!!**
DECLARE @destPrefix NVARCHAR(255) = 'JDE_CRP.CRPDTA';


SELECT  'ALTER INDEX ' + QUOTENAME(sys.indexes.name) + ' ON ' + @destPrefix + '.' + QUOTENAME(sys.objects.name) + ' DISABLE;'
FROM
    @tableList TL
        INNER JOIN sys.objects ON TL.tableName = sys.objects.name
        INNER JOIN sys.indexes ON sys.indexes.object_id = sys.objects.object_id
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'
        AND sys.objects.type_desc = 'USER_TABLE'

UNION ALL

SELECT 'TRUNCATE TABLE ' + @destPrefix + '.' + QUOTENAME(tableName) + ';' FROM @tableList

UNION ALL

SELECT 'INSERT INTO ' + @destPrefix + '.' + QUOTENAME(tableName) + ' SELECT * FROM ' + @sourcePrefix + '.' + QUOTENAME(tableName) FROM @tableList

UNION ALL

SELECT  'ALTER INDEX ' + QUOTENAME(sys.indexes.name) + ' ON ' + @destPrefix + '.' + QUOTENAME(sys.objects.name) + ' REBUILD;'
FROM
    @tableList TL
        INNER JOIN sys.objects ON TL.tableName = sys.objects.name
        INNER JOIN sys.indexes ON sys.indexes.object_id = sys.objects.object_id
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'
        AND sys.objects.type_desc = 'USER_TABLE'