Script to create script to copy datatable between two database

Script to create script to copy data from two datatable when tables are equal. This code is very useful when partially we save data.We can delete data after some date but we must have a column date.

Read the conditions to run this script.

-------------------------------
--  Script to create script to copy datatable betweeen two database
--
--  Condition:
--        We must have a primary key;
--        Primary key must be int;
--        If we use Linked server, we can not use XML column
--        We must run this script on database source
--
-------------------------------

-------------------------------
-- SET Parameters
DECLARE @DBSource as nvarchar(100);
DECLARE @SchemaSource as nvarchar(100);
DECLARE @TableSource as nvarchar(100);
DECLARE @KeyIDSource as nvarchar(100);
DECLARE @CopyByRun as int;
DECLARE @DeleteOldDate as int;
DECLARE @DeleteOldDateColumn as nvarchar(100);
DECLARE @DeleteOldDateKeepMonths as nvarchar(100);

DECLARE @DBDestination as nvarchar(100);
DECLARE @SchemaDestination as nvarchar(100);
DECLARE @TableDestination as nvarchar(100);


SET @DBSource = 'NORTHWND';
SET @SchemaSource = 'dbo';
SET @TableSource = 'Orders';
SET @KeyIDSource = 'OrderID';
SET @CopyByRun = 100000;
SET @DeleteOldDate = 1;
SET @DeleteOldDateColumn = 'OrderDate';
SET @DeleteOldDateKeepMonths = 1;

SET @DBDestination = 'NorthWindNEW';
SET @SchemaDestination = 'dbo';
SET @TableDestination = 'Orders';
-------------------------------

SET NOCOUNT ON;
DECLARE @KeyId AS nvarchar(100);
DECLARE @TableSourceFullName AS nvarchar(500);
DECLARE @TableDestinationFullName AS nvarchar(500);

SET @KeyId = '@'+@KeyIDSource;
SET @TableSourceFullName = @DBSource+'.'+@SchemaSource+'.'+@TableSource;
SET @TableDestinationFullName = @DBDestination+'.'+@SchemaDestination+'.'+@TableDestination;

PRINT '-------------------------------------------------'
PRINT '-- Copy data from : ' + @TableSourceFullName
PRINT '--             to : ' + @TableDestinationFullName
PRINT '--'
PRINT '-- Each time this script copy ' + cast(@CopyByRun as nvarchar(100)) + ' rows'
if (@DeleteOldDate=1) PRINT '-- Delete old data of ' + @TableSourceFullName + ' after ' + cast(@DeleteOldDateKeepMonths as nvarchar(100)) + ' month'
PRINT '--'
PRINT '-- Sozezzo ' + cast(GETDATE() as nvarchar(100));
PRINT '-------------------------------------------------'
PRINT ''

BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
SELECT
     TABLE_SCHEMA
    ,TABLE_NAME
    ,ORDINAL_POSITION AS rownumber
    ,COLUMN_NAME AS 'ColumnName'
    ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
    ,IS_NULLABLE
INTO #TMP
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaSource AND TABLE_NAME = @TableSource;


DECLARE @SQL AS NVARCHAR(MAX);
DECLARE @ColumnList AS NVARCHAR(MAX);
SET @ColumnList  = ''

SELECT @ColumnList = @ColumnList
        + CASE WHEN @ColumnList = '' THEN ' ' ELSE ', ' END
  + '['+ColumnName+']'
FROM #TMP WHERE ColumnName <> 'Timestamp';

 

PRINT 'DECLARE '+ @KeyId + ' as int;';
PRINT ''
PRINT 'SELECT ' + @KeyId + ' = ISNULL(MAX('+@KeyIDSource+'),0) FROM '+@TableDestinationFullName+';';
PRINT ''
PRINT 'SET IDENTITY_INSERT '+@TableDestinationFullName+' ON;'
PRINT ''
PRINT '-- INSERT DATA'
PRINT 'INSERT INTO ' + @TableDestinationFullName ;
PRINT '             ('+ @ColumnList + ')'
PRINT 'SELECT TOP ('+cast(@CopyByRun as nvarchar(100))+') '
PRINT '              '+ @ColumnList
PRINT 'FROM  ' + @TableSourceFullName
PRINT 'WHERE ('+@KeyIDSource+' > '+@KeyId+')'
PRINT 'ORDER BY '+@KeyIDSource+';'
PRINT ''
PRINT 'SET IDENTITY_INSERT '+@TableDestinationFullName+' OFF;'

IF (@DeleteOldDate = 1)
BEGIN

PRINT ''
PRINT '-- DELETE OLD DATA'
PRINT 'DECLARE @n INT;'
PRINT 'SELECT ' + @KeyId + ' = ISNULL(MAX('+@KeyIDSource+'),0) FROM '+@TableDestinationFullName+';';
PRINT 'SELECT TOP 1 @n = MAX(['+@KeyIDSource+']) FROM '+@TableSourceFullName;
PRINT 'WHERE ['+@DeleteOldDateColumn+'] < DATEADD(month, -'+cast(@DeleteOldDateKeepMonths as nvarchar(100))+', GETUTCDATE());'
PRINT ''
PRINT 'IF ( @n IS NOT NULL )'
PRINT 'BEGIN'
PRINT '    IF ('+@KeyId+' > @n ) SET '+@KeyId+' = @n;'
PRINT '    -- PRINT '+@KeyId+';'
PRINT '    DELETE FROM '+ @TableSourceFullName + ' WHERE '+@KeyIDSource+' < ' + @KeyId + ';'
PRINT 'END'
PRINT ''
PRINT '-- SELECT TOP 100 * FROM '+ @TableDestinationFullName+';';
PRINT '-- SELECT TOP 100 * FROM '+ @TableSourceFullName+';';
END

Result:

-------------------------------------------------
-- Copy data from : NORTHWND.dbo.Orders
--             to : NorthWindNEW.dbo.Orders
--
-- Each time this script copy 100000 rows
-- Delete old data of NORTHWND.dbo.Orders after 1 month
--
-- Sozezzo Apr 28 2015 11:38AM
-------------------------------------------------
 
DECLARE @OrderID as int;
 
SELECT @OrderID = ISNULL(MAX(OrderID),0) FROM NorthWindNEW.dbo.Orders;
 
SET IDENTITY_INSERT NorthWindNEW.dbo.Orders ON;
 
-- INSERT DATA
INSERT INTO NorthWindNEW.dbo.Orders
             ( [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])
SELECT TOP (100000)
               [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]
FROM  NORTHWND.dbo.Orders
WHERE (OrderID > @OrderID)
ORDER BY OrderID;
 
SET IDENTITY_INSERT NorthWindNEW.dbo.Orders OFF;
 
-- DELETE OLD DATA
DECLARE @n INT;
SELECT @OrderID = ISNULL(MAX(OrderID),0) FROM NorthWindNEW.dbo.Orders;
SELECT TOP 1 @n = MAX([OrderID]) FROM NORTHWND.dbo.Orders
WHERE [OrderDate] < DATEADD(month, -1, GETUTCDATE());
 
IF ( @n IS NOT NULL )
BEGIN
    IF (@OrderID > @n ) SET @OrderID = @n;
    -- PRINT @OrderID;
    DELETE FROM NORTHWND.dbo.Orders WHERE OrderID < @OrderID;
END
 
-- SELECT TOP 100 * FROM NorthWindNEW.dbo.Orders;
-- SELECT TOP 100 * FROM NORTHWND.dbo.Orders;