根据表的主键字段和数据字段从其它数据库同步相同表的数据
- 作者: 约德尔人郭小4
- 来源: 51数据库
- 2022-08-17
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
推荐阅读