🏚️Chuyển dữ liệu từ chi nhánh sang chi nhánh khác

• Chay CSDL

// RUN CSDL Cần chuyển 
USE VTTECHDEMO

• Chạy Query

DECLARE INT = 1
DECLARE @ INT = 1
DECLARE @TotalBranch INT = ISNULL((SELECT COUNT(ID) FROM VTT_Branch), 0)
SELECT TotalBranch = @TotalBranch
IF(@TotalBranch > 1 AND @FromBranchID<> @ToBranchID)
BEGIN

	SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
	INTO #DTMAIN
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME  = 'BranchID' 
		OR COLUMN_NAME  = 'Branch_ID'

	--SELECT * FROM #DTMAIN

	DECLARE @TableName NVARCHAR(200) = '', 
			@COLUMN_NAME NVARCHAR(200) = '', 
			@COLUMN_TYPE NVARCHAR(200) = ''
	DECLARE @query NVARCHAR(MAX) = ''
	
	WHILE((SELECT COUNT(TABLE_NAME) FROM #DTMAIN) > 0)
	BEGIN
		SET @query = ''

		SELECT TOP(1) @TableName=TABLE_NAME
			,@COLUMN_NAME=COLUMN_NAME
			,@COLUMN_TYPE=DATA_TYPE
		FROM #DTMAIN

		IF(@COLUMN_TYPE = 'int')
			BEGIN
				SET @query = '
					UPDATE '+ @TableName + ' 
					SET '+@COLUMN_NAME+' = CAST( '+ CAST(@ToBranchID AS NVARCHAR) + ' AS INT)
					WHERE '+ @COLUMN_NAME + ' = CAST( '+ CAST(@FromBranchID AS NVARCHAR) + ' AS INT)
				'
				EXECUTE(@query)
				SELECT 'UPDATE SUCCESS ' + @TableName

			END 
		DELETE TOP(1) FROM #DTMAIN
	END

	DROP TABLE #DTMAIN
END