Script to Create Script to Copy Datatable Between Two Database

  1
  2-------------------------------
  3--  Script to create script to copy datatable betweeen two database
  4--
  5--  Condition:
  6--        We must have a primary key;
  7--        Primary key must be int;
  8--        If we use Linked server, we can not use XML column
  9--        We must run this script on database source
 10--
 11-------------------------------
 12
 13-------------------------------
 14-- SET Parameters
 15DECLARE @DBSource as nvarchar(100);
 16DECLARE @SchemaSource as nvarchar(100);
 17DECLARE @TableSource as nvarchar(100);
 18DECLARE @KeyIDSource as nvarchar(100);
 19DECLARE @CopyByRun as int;
 20DECLARE @DeleteOldDate as int;
 21DECLARE @DeleteOldDateColumn as nvarchar(100);
 22DECLARE @DeleteOldDateKeepMonths as nvarchar(100);
 23
 24DECLARE @DBDestination as nvarchar(100);
 25DECLARE @SchemaDestination as nvarchar(100);
 26DECLARE @TableDestination as nvarchar(100);
 27
 28SET @DBSource = 'NORTHWND';
 29SET @SchemaSource = 'dbo';
 30SET @TableSource = 'Orders';
 31SET @KeyIDSource = 'OrderID';
 32SET @CopyByRun = 100000;
 33SET @DeleteOldDate = 1;
 34SET @DeleteOldDateColumn = 'OrderDate';
 35SET @DeleteOldDateKeepMonths = 1;
 36
 37SET @DBDestination = 'NorthWindNEW';
 38SET @SchemaDestination = 'dbo';
 39SET @TableDestination = 'Orders';
 40-------------------------------
 41
 42SET NOCOUNT ON;
 43DECLARE @KeyId AS nvarchar(100);
 44DECLARE @TableSourceFullName AS nvarchar(500);
 45DECLARE @TableDestinationFullName AS nvarchar(500);
 46
 47SET @KeyId = <a href="mailto:'@'+@KeyIDSource">'@'+@KeyIDSource</a>;
 48SET @TableSourceFullName = @DBSource+'.'+@SchemaSource+'.'+@TableSource;
 49SET @TableDestinationFullName = @DBDestination+'.'+@SchemaDestination+'.'+@TableDestination;
 50
 51PRINT '-------------------------------------------------'
 52PRINT '-- Copy data from : ' + @TableSourceFullName
 53PRINT '--             to : ' + @TableDestinationFullName
 54PRINT '--'
 55PRINT '-- Each time this script copy ' + cast(@CopyByRun as nvarchar(100)) + ' rows'
 56if (@DeleteOldDate=1) PRINT '-- Delete old data of ' + @TableSourceFullName + ' after ' + cast(@DeleteOldDateKeepMonths as nvarchar(100)) + ' month'
 57PRINT '--'
 58PRINT '-- Sozezzo ' + cast(GETDATE() as nvarchar(100));
 59PRINT '-------------------------------------------------'
 60PRINT ''
 61
 62BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
 63SELECT
 64     TABLE_SCHEMA
 65    ,TABLE_NAME
 66    ,ORDINAL_POSITION AS rownumber
 67    ,COLUMN_NAME AS 'ColumnName'
 68    ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
 69    ,IS_NULLABLE
 70INTO #TMP
 71FROM INFORMATION_SCHEMA.COLUMNS
 72WHERE TABLE_SCHEMA = @SchemaSource AND TABLE_NAME = @TableSource;
 73
 74DECLARE @SQL AS NVARCHAR(MAX);
 75DECLARE @ColumnList AS NVARCHAR(MAX);
 76SET @ColumnList  = ''
 77
 78SELECT @ColumnList = @ColumnList
 79        + CASE WHEN @ColumnList = '' THEN ' ' ELSE ', ' END
 80  + '['+ColumnName+']'
 81FROM #TMP WHERE ColumnName <> 'Timestamp';
 82
 83PRINT 'DECLARE '+ @KeyId + ' as int;';
 84PRINT ''
 85PRINT 'SELECT ' + @KeyId + ' = ISNULL(MAX(<a href="mailto:'+@KeyIDSource+'),0">'+@KeyIDSource+'),0</a>) FROM <a href="mailto:'+@TableDestinationFullName+';'">'+@TableDestinationFullName+';'</a>;
 86PRINT ''
 87PRINT 'SET IDENTITY_INSERT <a href="mailto:'+@TableDestinationFullName+'">'+@TableDestinationFullName+'</a> ON;'
 88PRINT ''
 89PRINT '-- INSERT DATA'
 90PRINT 'INSERT INTO ' + @TableDestinationFullName ;
 91PRINT '             ('+ @ColumnList + ')'
 92PRINT 'SELECT TOP ('+cast(@CopyByRun as nvarchar(100))+') '
 93PRINT '              '+ @ColumnList
 94PRINT 'FROM  ' + @TableSourceFullName
 95PRINT 'WHERE (<a href="mailto:'+@KeyIDSource+'">'+@KeyIDSource+'</a> > <a href="mailto:'+@KeyId+')'">'+@KeyId+')'</a>
 96PRINT 'ORDER BY <a href="mailto:'+@KeyIDSource+';'">'+@KeyIDSource+';'</a>
 97PRINT ''
 98PRINT 'SET IDENTITY_INSERT <a href="mailto:'+@TableDestinationFullName+'">'+@TableDestinationFullName+'</a> OFF;'
 99
100IF (@DeleteOldDate = 1)
101BEGIN
102
103PRINT ''
104PRINT '-- DELETE OLD DATA'
105PRINT 'DECLARE @n INT;'
106PRINT 'SELECT ' + @KeyId + ' = ISNULL(MAX(<a href="mailto:'+@KeyIDSource+'),0">'+@KeyIDSource+'),0</a>) FROM <a href="mailto:'+@TableDestinationFullName+';'">'+@TableDestinationFullName+';'</a>;
107PRINT 'SELECT TOP 1 @n = MAX(['+@KeyIDSource+']) FROM <a href="mailto:'+@TableSourceFullName">'+@TableSourceFullName</a>;
108PRINT 'WHERE ['+@DeleteOldDateColumn+'] < DATEADD(month, -'+cast(@DeleteOldDateKeepMonths as nvarchar(100))+', GETUTCDATE());'
109PRINT ''
110PRINT 'IF ( @n IS NOT NULL )'
111PRINT 'BEGIN'
112PRINT '    IF (<a href="mailto:'+@KeyId+'">'+@KeyId+'</a> > @n ) SET <a href="mailto:'+@KeyId+'">'+@KeyId+'</a> = @n;'
113PRINT '    -- PRINT <a href="mailto:'+@KeyId+';'">'+@KeyId+';'</a>
114PRINT '    DELETE FROM '+ @TableSourceFullName + ' WHERE <a href="mailto:'+@KeyIDSource+'">'+@KeyIDSource+'</a> < ' + @KeyId + ';'
115PRINT 'END'
116PRINT ''
117PRINT '-- SELECT TOP 100 * FROM '+ @TableDestinationFullName+';';
118PRINT '-- SELECT TOP 100 * FROM '+ @TableSourceFullName+';';
119END

 1
 2-------------------------------------------------
 3-- Copy data from : NORTHWND.dbo.Orders
 4--             to : NorthWindNEW.dbo.Orders
 5--
 6-- Each time this script copy 100000 rows
 7-- Delete old data of NORTHWND.dbo.Orders after 1 month
 8--
 9-- Sozezzo Apr 28 2015 11:38AM
10-------------------------------------------------
11
12DECLARE @OrderID as int;
13
14SELECT @OrderID = ISNULL(MAX(OrderID),0) FROM NorthWindNEW.dbo.Orders;
15
16SET IDENTITY_INSERT NorthWindNEW.dbo.Orders ON;
17
18-- INSERT DATA
19INSERT INTO NorthWindNEW.dbo.Orders
20             ( [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry])
21SELECT TOP (100000)
22               [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]
23FROM  NORTHWND.dbo.Orders
24WHERE (OrderID > @OrderID)
25ORDER BY OrderID;
26
27SET IDENTITY_INSERT NorthWindNEW.dbo.Orders OFF;
28
29-- DELETE OLD DATA
30DECLARE @n INT;
31SELECT @OrderID = ISNULL(MAX(OrderID),0) FROM NorthWindNEW.dbo.Orders;
32SELECT TOP 1 @n = MAX([OrderID]) FROM NORTHWND.dbo.Orders
33WHERE [OrderDate] < DATEADD(month, -1, GETUTCDATE());
34
35IF ( @n IS NOT NULL )
36BEGIN
37    IF (@OrderID > @n ) SET @OrderID = @n;
38    -- PRINT @OrderID;
39    DELETE FROM NORTHWND.dbo.Orders WHERE OrderID < @OrderID;
40END
41
42-- SELECT TOP 100 * FROM NorthWindNEW.dbo.Orders;
43-- SELECT TOP 100 * FROM NORTHWND.dbo.Orders;