根据表的主键字段和数据字段从其它数据库同步相同表的数据

[sql]代码库

USE [CMS2]
GO
 
DECLARE @sql NVARCHAR(MAX)
 
SET @sql = N'MERGE [dbo].[RolePermission] target
            USING [CMS].[dbo].[RolePermission] source
            ON target.[' + STUFF((
            SELECT N'] AND target.[' + [COLUMN_NAME] + N']=source.[' + [COLUMN_NAME]
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE SUBSTRING([CONSTRAINT_NAME], 1, 2) = 'PK'
                AND [TABLE_NAME] = 'RolePermission'
            FOR XML PATH('')
            ), 1, 14, N'') + '] 
            WHEN MATCHED
            THEN UPDATE SET target.[' + STUFF((
            SELECT N'],target.[' + [COLUMN_NAME] + N']=source.[' + [COLUMN_NAME]
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE [TABLE_NAME] = 'RolePermission'
            FOR XML PATH('')
            ), 1, 10, N'') + ']
            WHEN NOT MATCHED
            THEN INSERT VALUES([' + STUFF((
            SELECT N'],[' + [COLUMN_NAME]
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE [TABLE_NAME] = 'RolePermission'
            FOR XML PATH('')
            ), 1, 3, N'') + N'])   
            WHEN NOT MATCHED BY SOURCE THEN   
            DELETE;'
 
EXEC sp_executesql @sql