SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[copy_data_between_databases]
@sourceDB NVARCHAR(100),
@destinationDB NVARCHAR(100)
AS
BEGIN
-- Declare variables
DECLARE @done INT = 0;
DECLARE @tableName NVARCHAR(100);
DECLARE @sql NVARCHAR(MAX);
DECLARE @columnName NVARCHAR(100);
DECLARE @schemaName NVARCHAR(100);
-- Create dynamic SQL statements to create and populate global temporary tables
DECLARE @sql1 NVARCHAR(MAX);
DECLARE @sql2 NVARCHAR(MAX);
DROP TABLE IF EXISTS ##tblA
DROP TABLE IF EXISTS ##tblB
IF CURSOR_STATUS('global','cur') >= -1
BEGIN
IF CURSOR_STATUS('global','cur') > -1
BEGIN
CLOSE cur
END
DEALLOCATE cur
END
-- Handlers
DECLARE cur CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_catalog = @destinationDB; -- Get all tables in dbA
-- Open cursor
OPEN cur;
-- Loop through tables
FETCH NEXT FROM cur INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get column name and schema name
SELECT @columnName = COLUMN_NAME, @schemaName = TABLE_SCHEMA
FROM information_schema.columns
WHERE TABLE_CATALOG = @destinationDB AND TABLE_NAME = @tableName
ORDER BY ORDINAL_POSITION
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
---------------Get Table Columns----------------
SET @sql1 = N'
SELECT *
INTO ##tblA
FROM ' + QUOTENAME(@sourceDB) + '.information_schema.columns
WHERE table_schema = ''dbo'' AND table_name = '''+@tableName+''';
';
SET @sql2 = N'
SELECT *
INTO ##tblB
FROM ' + QUOTENAME(@destinationDB) + '.information_schema.columns
WHERE table_schema = ''dbo'' AND table_name = '''+@tableName+''';
';
-- Execute dynamic SQL statements
EXEC sp_executesql @sql1;
EXEC sp_executesql @sql2;
Declare @CommonColumns varchar(max) =null;
Select @CommonColumns= Coalesce(@CommonColumns + ',', '') + b.Column_Name FROM ##tblA a
inner JOIN ##tblB b ON a.Column_Name COLLATE Arabic_CI_AI=b.Column_Name COLLATE Arabic_CI_AI;
-- Drop global temporary tables
EXEC('
DROP TABLE ##tblA;
DROP TABLE ##tblB;
');
---------------------------End Table Columns---------------
-- Generate dynamic SQL query
SET @sql = N'INSERT INTO ' + QUOTENAME(@destinationDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) +'('+@CommonColumns+')'+
N' SELECT '+@CommonColumns+' FROM ' + QUOTENAME(@sourceDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) +
N' WHERE NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@destinationDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) +
N' WHERE '+ QUOTENAME(@sourceDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'.' + QUOTENAME(@columnName) + N' = '+ QUOTENAME(@destinationDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'.' + QUOTENAME(@columnName) + N');'
PRINT 'Executing SQL: ' + @sql;
-- Execute dynamic SQL query
EXEC sp_executesql @sql;
PRINT 'Total Inserted Records in '+QUOTENAME(@destinationDB) + N'.' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName)+' = '+CAST(@@ROWCOUNT AS NVARCHAR(10));
FETCH NEXT FROM cur INTO @tableName;
END;
-- Close cursor
CLOSE cur;
DEALLOCATE cur;
END;