T-SQL query to copy data from one database to another database

/*
	Author		:	Brahmanand Shukla
	Date		:	28-Oct-2019
	Purpose		:	T-SQL query to copy the rows of all the tables from one database to another database 
					only if they have rows in Source DB and exact same Schema in both Source and Destination DB.
*/

; WITH cte_All_Tables_With_Foreign_Key
-- Get all the tables (from Destination DB) having foreign key. Ignore the self-referencing.
AS
(
	SELECT PAR_SCH.[name]			AS	[Parent_Schema_Name]
		, PAR_TAB.[name]			AS	[Parent_Table_Name]
		, REF_SCH.[name]			AS	[Referenced_Schema_Name]
		, REF_TAB.[name]			AS	[Referenced_Table_Name]
		, FK.[parent_object_id]		AS	[parent_object_id]
		, FK.[referenced_object_id]	AS	[referenced_object_id]
	FROM destination.sys.foreign_keys FK
		INNER JOIN destination.sys.tables PAR_TAB
			ON PAR_TAB.[object_id] = FK.[parent_object_id]
		INNER JOIN destination.sys.schemas PAR_SCH
			ON PAR_SCH.[schema_id] = PAR_TAB.[schema_id]
		INNER JOIN destination.sys.tables REF_TAB
			ON REF_TAB.[object_id] = FK.[referenced_object_id]
		INNER JOIN destination.sys.schemas REF_SCH
			ON REF_SCH.[schema_id] = REF_TAB.[schema_id]
	WHERE FK.[type] = 'F'
		AND FK.[parent_object_id] <> [referenced_object_id]
		AND PAR_TAB.type = 'U'
		AND REF_TAB.type = 'U'
)
, cte_Find_All_Referenced_Tables_In_Sequence
/*
	Recursive CTE :
	 
	Find the sequence of each referenced table. 
	For e.g Table1 is referenced with Table2 and Table2 is referenced with Table3 
	then Table3 should be assigned Sequence as 1, 
	Table2 should be assigned Sequence as 2 
	and Table1 should be assigned Sequence as 3
*/
AS
(
	SELECT FK1.[Parent_Schema_Name]
		, FK1.[Parent_Table_Name]
		, FK1.[Referenced_Schema_Name]
		, FK1.[Referenced_Table_Name]
		, FK1.[parent_object_id]
		, FK1.[referenced_object_id]
		, 1 AS [Iteration_Sequence_No]
	FROM cte_All_Tables_With_Foreign_Key FK1
		LEFT JOIN cte_All_Tables_With_Foreign_Key FK2
			ON FK1.[parent_object_id] = FK2.[referenced_object_id]
	WHERE FK2.[parent_object_id] IS NULL
	UNION ALL
	SELECT FK.[Parent_Schema_Name]
		, FK.[Parent_Table_Name]
		, FK.[Referenced_Schema_Name]
		, FK.[Referenced_Table_Name]
		, FK.[parent_object_id]
		, FK.[referenced_object_id]
		, CTE.[Iteration_Sequence_No] + 1 AS [Iteration_Sequence_No]
	FROM cte_All_Tables_With_Foreign_Key FK
		INNER JOIN cte_Find_All_Referenced_Tables_In_Sequence CTE
			ON FK.[parent_object_id] = CTE.[referenced_object_id]
	WHERE FK.[referenced_object_id] <> CTE.[parent_object_id]
)
, cte_All_Tables
/* 
	Merge all tables (such as Tables with Foreign Key and Tables without Foreign Key) along with their sort order. 
	Tables without Foreign Key and Tables with root level Foreign Key should be given the least sort order
*/
AS
(
	SELECT [Referenced_Schema_Name]		AS	[TABLE_SCHEMA]
		, [Referenced_Table_Name]		AS	[TABLE_NAME]
		, 1								AS	[SORT_ORDER]
		, [Iteration_Sequence_No]
	FROM cte_Find_All_Referenced_Tables_In_Sequence
	UNION
	SELECT STGA.[Parent_Schema_Name]	AS	[TABLE_SCHEMA]
		, STGA.[Parent_Table_Name]		AS	[TABLE_NAME]
		, 2								AS	[SORT_ORDER]
		, 0								AS [Iteration_Sequence_No]
	FROM cte_Find_All_Referenced_Tables_In_Sequence STGA
		LEFT JOIN cte_Find_All_Referenced_Tables_In_Sequence STGB
			ON STGB.[referenced_object_id] = STGA.[parent_object_id]
	WHERE STGB.[Iteration_Sequence_No] IS NULL
	UNION ALL
	SELECT SCH.[name]					AS	[TABLE_SCHEMA]
		, TAB.[name]					AS	[TABLE_NAME]
		, 3								AS	[SORT_ORDER]
		, 0								AS	[Iteration_Sequence_No]
	FROM destination.sys.tables TAB
		INNER JOIN destination.sys.schemas SCH
			ON SCH.[schema_id] = TAB.[schema_id]
		LEFT JOIN cte_Find_All_Referenced_Tables_In_Sequence STGB
			ON (STGB.[parent_object_id] = TAB.[object_id] OR STGB.[referenced_object_id] = TAB.[object_id])
	WHERE STGB.[Iteration_Sequence_No] IS NULL
)
, cte_Source_Table
/* 
	Get all the tables (from Source DB) along with following attributes :
	1) Full Name in the format <database>.<schema>.<tablename>
	2) No of columns
	3) Complete column list to be used in "INSERT INTO Destination... SELECT .... FROM Source"
	4) Checksum of all the important column attributes forming the Table Schema. This will be used to compare the tables between Source DB and Destination DB having exact Schema.

	*** Table having no rows will be filtered out / ignored.
*/
AS
(
	SELECT [TAB].[TABLE_CATALOG]
		, [TAB].[TABLE_SCHEMA]
		, [TAB].[TABLE_NAME]
		, QUOTENAME([TAB].[TABLE_CATALOG]) + '.' + QUOTENAME([TAB].[TABLE_SCHEMA]) + '.' + QUOTENAME([TAB].[TABLE_NAME]) AS [TABLE_FULL_NAME]
		, [COL].[NO_OF_COLUMNS]
		, 
			STUFF(
					(
						SELECT ', ' + QUOTENAME(COL.[COLUMN_NAME]) + ' '
						FROM source.INFORMATION_SCHEMA.COLUMNS COL
						WHERE COL.[TABLE_SCHEMA] =	TAB.[TABLE_SCHEMA]
							AND COL.[TABLE_NAME] =	TAB.[TABLE_NAME]
						ORDER BY COL.[ORDINAL_POSITION] ASC
						FOR XML PATH ('')
					), 1, 1, '') AS [COLUMN_NAME_LIST]
		, COL.[COLUMNS_CHECKSUM]
	FROM source.INFORMATION_SCHEMA.TABLES [TAB]
		CROSS APPLY
			(
				SELECT COUNT(1) AS [NO_OF_COLUMNS]
					, SUM(CAST(CHECKSUM([COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH]
										, [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX]
										, [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_NAME], [COLLATION_NAME]) 
										AS NUMERIC)) AS [COLUMNS_CHECKSUM]
				FROM source.INFORMATION_SCHEMA.COLUMNS [COL]
				WHERE [COL].[TABLE_SCHEMA]	=	[TAB].[TABLE_SCHEMA]
					AND [COL].[TABLE_NAME]	=	[TAB].[TABLE_NAME]
			) [COL]
	WHERE EXISTS
			(
				SELECT 1
				FROM source.sys.tables T
					INNER JOIN source.sys.schemas S
						ON S.[schema_id] = T.[schema_id]
					INNER JOIN source.sys.partitions P
						ON P.[object_id] = T.[object_id]
				WHERE S.[name]		=	[TAB].[TABLE_SCHEMA]
					AND T.[name]	=	[TAB].[TABLE_NAME]
					AND P.[index_id] < 2
				HAVING SUM(P.[rows]) > 0
			)
)
, cte_Destination_Table
/* 
	Get all the tables (from Destination DB) along with following attributes :
	1) Full Name in the format <database>.<schema>.<tablename>
	2) No of columns
	3) Checksum of all the important column attributes forming the Table Schema. This will be used to compare the tables between Source DB and Destination DB having exact Schema.
	4) Attributes indicating whether the table has IDENTITY column. This is to manage the IDENTITY_INSERT ON/OFF
*/
AS
(
	SELECT [TAB].[TABLE_CATALOG]
		, [TAB].[TABLE_SCHEMA]
		, [TAB].[TABLE_NAME]
		, QUOTENAME([TAB].[TABLE_CATALOG]) + '.' + QUOTENAME([TAB].[TABLE_SCHEMA]) + '.' + QUOTENAME([TAB].[TABLE_NAME]) AS [TABLE_FULL_NAME]
		, [COL].[NO_OF_COLUMNS]
		, 
			CASE
				WHEN EXISTS 
						(
							SELECT 1
							FROM destination.sys.tables T
								INNER JOIN destination.sys.schemas S
									ON S.[schema_id] = T.[schema_id]
								INNER JOIN destination.sys.identity_columns I
									ON I.[object_id] = T.[object_id]
							WHERE S.[name]		=	[TAB].[TABLE_SCHEMA]
								AND T.[name]	=	[TAB].[TABLE_NAME]
								AND I.[is_identity] = 1
						)
					THEN 1
				ELSE 0
			END AS [TABLE_HAS_IDENTITY_COLUMN]
		, 
			STUFF(
					(
						SELECT ', ' + QUOTENAME(COL.[COLUMN_NAME]) + ' '
						FROM destination.INFORMATION_SCHEMA.COLUMNS COL
						WHERE COL.[TABLE_SCHEMA] =	TAB.[TABLE_SCHEMA]
							AND COL.[TABLE_NAME] =	TAB.[TABLE_NAME]
						ORDER BY COL.[ORDINAL_POSITION] ASC
						FOR XML PATH ('')
					), 1, 1, '') AS [COLUMN_NAME_LIST]
		, COL.[COLUMNS_CHECKSUM]
	FROM destination.INFORMATION_SCHEMA.TABLES [TAB]
		CROSS APPLY
			(
				SELECT COUNT(1) AS [NO_OF_COLUMNS]
					, SUM(CAST(CHECKSUM([COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH]
										, [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX]
										, [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_NAME], [COLLATION_NAME]) 
										AS NUMERIC)) AS [COLUMNS_CHECKSUM]
				FROM destination.INFORMATION_SCHEMA.COLUMNS [COL]
				WHERE [COL].[TABLE_SCHEMA]	=	[TAB].[TABLE_SCHEMA]
					AND [COL].[TABLE_NAME]	=	[TAB].[TABLE_NAME]
			) [COL]
)
/*
	Following SELECT statement will compare the tables between Source DB and Destination DB and fetch the tables having exact same Schema

	*** Salient features of the T-SQL "INSERT INTO Destination... SELECT .... FROM Source" generated from the below SELECT query :
	1) "SET XACT_ABORT ON" is used to rollback the transaction in case of error
	2) "TRY .. CATCH" block is used for each INSERT to catch the errors
	3) "IDENTITY_INSERT" setting in the generated query is dynamic
	4) All the rows from Destination DB to Source DB will written in the same Sort Order as defined in Primary Key in the Source DB
	5) Generated T-SQL query will be well formatted and each INSERT statement shall be properly commented for better readability
	6) Generated T-SQL query shall also include the statement to show the status of execution of each query viz. Started, Completed or Failed

	*** Disclaimer :
	Executing the generated T-SQL query multiple times will cause the duplication of rows hence take the necessary caution. 
	However, this can be made dynamic and rerunnable if all the tables has the Primary Key.
*/
SELECT ROW_NUMBER() OVER (ORDER BY TBL_SEQ.[Sort_Order] ASC, TBL_SEQ.[Iteration_Sequence_No] DESC)	
									AS	[TABLE_SEQUENCE_NO]
	, [DEST_TAB].[TABLE_SCHEMA]		AS	[TABLE_SCHEMA]
	, [DEST_TAB].[TABLE_NAME]		AS	[TABLE_NAME]
	, 
		(CASE
			WHEN ROW_NUMBER() OVER (ORDER BY TBL_SEQ.[Sort_Order] ASC, TBL_SEQ.[Iteration_Sequence_No] DESC) = 1
				THEN 'SET XACT_ABORT ON;' + CHAR(13) + CHAR(10) + 'SET NOCOUNT ON;'
			ELSE ''
		END + CHAR(13) + CHAR(10) + 
		'-- ***************************************** INSERT SCRIPT FOR ' + [DEST_TAB].[TABLE_FULL_NAME] + ' -******* START *****************' + CHAR(13) + CHAR(10) + 
		'SELECT ''INSERT SCRIPT EXECUTION STARTED FOR ' + [DEST_TAB].[TABLE_FULL_NAME] + '''' + CHAR(13) + CHAR(10) + 
		'BEGIN TRY' + CHAR(13) + CHAR(10) + 
		CASE
			WHEN [DEST_TAB].[TABLE_HAS_IDENTITY_COLUMN] = 1
				THEN CHAR(9) + 'SET IDENTITY_INSERT ' + [DEST_TAB].[TABLE_FULL_NAME] + ' ON;' + CHAR(13) + CHAR(10)
			ELSE ''
		END + 
		CHAR(9) + 'INSERT INTO ' + [DEST_TAB].[TABLE_FULL_NAME] + '(' + DEST_TAB.[COLUMN_NAME_LIST] + ')'+ CHAR(13) + CHAR(10) + 
		CHAR(9) + 'SELECT ' + DEST_TAB.[COLUMN_NAME_LIST] + ' FROM ' + [SRC_TAB].[TABLE_FULL_NAME] + CHAR(13) + CHAR(10) + 
		CHAR(9) + 
		CASE
			WHEN [SRC_PK].[CONSTRAINT_NAME] IS NOT NULL
				THEN 'ORDER BY' 
					+ STUFF(
							(
								SELECT ', ' + QUOTENAME(COL.[name]) + ' ' + 
									CASE
										WHEN IX_COL.[is_descending_key] = 1
											THEN 'DESC'
										ELSE 'ASC'
									END
								FROM source.sys.indexes IX 
									INNER JOIN source.sys.index_columns IX_COL
										ON IX_COL.[object_id] = IX.[object_id]
									INNER JOIN source.sys.columns COL
										ON COL.[object_id] = IX_COL.[object_id]
										AND COL.[column_id] = IX_COL.[column_id]
								WHERE IX.[name] = [SRC_PK].[CONSTRAINT_NAME]
								FOR XML PATH ('')
							), 1, 1, '') 
			ELSE '' 
		END + CHAR(13) + CHAR(10) + 
		CASE
			WHEN [DEST_TAB].[TABLE_HAS_IDENTITY_COLUMN] = 1
				THEN CHAR(9) + 'SET IDENTITY_INSERT ' + [DEST_TAB].[TABLE_FULL_NAME] + ' OFF;' + CHAR(13) + CHAR(10)
			ELSE ''
		END + 
		CHAR(9) + 'SELECT ''INSERT SCRIPT EXECUTION COMPLETED FOR ' + [DEST_TAB].[TABLE_FULL_NAME] + '''' + CHAR(13) + CHAR(10) + 
		'END TRY' + CHAR(13) + CHAR(10) + 
		'BEGIN CATCH' + CHAR(13) + CHAR(10) + 
		CHAR(9) + 'SELECT ''INSERT SCRIPT EXECUTION FAILED FOR ' + [DEST_TAB].[TABLE_FULL_NAME] + '''' + CHAR(13) + CHAR(10) + 
		'END CATCH' + CHAR(13) + CHAR(10) + 
		'-- ***************************************** INSERT SCRIPT FOR ' + [DEST_TAB].[TABLE_FULL_NAME] + ' -******* END *****************' + CHAR(13) + CHAR(10) +
		'GO')
									AS	[TSQL_INSERT_SCRIPT]
FROM cte_Source_Table [SRC_TAB]
	INNER JOIN cte_Destination_Table [DEST_TAB]
		ON [DEST_TAB].[TABLE_SCHEMA]	=	[SRC_TAB].[TABLE_SCHEMA]
		AND [DEST_TAB].[TABLE_NAME]		=	[SRC_TAB].[TABLE_NAME]
		AND [DEST_TAB].[NO_OF_COLUMNS]	=	[SRC_TAB].[NO_OF_COLUMNS]
	INNER JOIN cte_All_Tables TBL_SEQ
		ON [TBL_SEQ].[TABLE_SCHEMA]		=	[SRC_TAB].[TABLE_SCHEMA]
		AND [TBL_SEQ].[TABLE_NAME]		=	[SRC_TAB].[TABLE_NAME]
	LEFT JOIN source.INFORMATION_SCHEMA.TABLE_CONSTRAINTS [SRC_PK] 
		ON [SRC_PK].[TABLE_SCHEMA]		=	[SRC_TAB].[TABLE_SCHEMA]
		AND [SRC_PK].[TABLE_NAME]		=	[SRC_TAB].[TABLE_NAME]
		AND [SRC_PK].[CONSTRAINT_TYPE] = 'PRIMARY KEY'
WHERE [SRC_TAB].[COLUMNS_CHECKSUM]		=	[DEST_TAB].[COLUMNS_CHECKSUM]
OPTION (MAXRECURSION 10)