业务场景
两个业务实体合并。假设业务实体为人员A和人员B，需要将两个人员的相关数据合并，且保留人员B，删掉人员A。
通过分析实际的场景，在库中存的相关业务数据都是通过人员ID关联人员的，而人员ID在库中是唯一的值。所以只需要全库所有表中查找人员A的 ID，替换成人员B，即可完成两个人员的合并需求(注意排除存储人员的数据表)。
具体实现
分两种：
- 一种是某表某字段只存储一位人员ID
- 一种是此字段存储多位人员ID(中间有分隔符)
第一种：
代码语言：javascript复制DECLARE @OldValue NVARCHAR(MAX) = '人员A-ID'
DECLARE @NewValue NVARCHAR(MAX) = '人员B-ID'
DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @UpdateStmt NVARCHAR(MAX)
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @aExists INT
DECLARE @SQLString NVARCHAR(1000)
--创建游标，获取库中所有(表名,列名)
DECLARE tableCursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (167, 175, 231, 239) -- 数据类型为char、varchar、nchar、nvarchar的列
-- CREATE TABLE #TempTable (
-- ID NVARCHAR(MAX),
-- CNAME NVARCHAR(MAX)
-- )
--
--获取游标中的行
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
--循环游标
WHILE @@FETCH_STATUS = 0
BEGIN
--用来排除某些表或者列
IF @TableName='TABLE-A' OR @ColumnName='COLUMN-A'
BEGIN
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
CONTINUE
END
--判断此表此列是否存在 值 等于 人员A-ID ，如果有替换值
SET @SqlQuery = 'SELECT @aExists=1 FROM ' QUOTENAME(@TableName) ' WHERE ' QUOTENAME(@ColumnName) ' = @OldValue'
EXEC sp_executesql @SqlQuery, N'@OldValue NVARCHAR(MAX), @aExists INT OUTPUT', @OldValue, @aExists OUTPUT
IF @aExists=1
BEGIN
--将相关的合并数据进行备份，备份表(bak_tran),此表结构应根据具体情况设计，以下只供参考。原本打算存在临时表（TempTable）
SET @SQLString = 'SELECT ID,''' @TableName ''',''' @ColumnName ''',''' @NewValue ''',''' @OldValue ''' FROM ' QUOTENAME(@TableName) ' WHERE ' QUOTENAME(@ColumnName) ' = @OldValue'
INSERT INTO [bak_tran](id,tname,cname,new,old) EXEC sp_executesql @SQLString,N'@OldValue NVARCHAR(MAX)',@OldValue
--将 人员A-ID 替换为 人以员B-ID
SET @UpdateStmt = 'UPDATE ' QUOTENAME(@TableName) ' SET ' QUOTENAME(@ColumnName) ' = CASE WHEN ' QUOTENAME(@ColumnName) ' =@OldValue THEN @NewValue ELSE ' QUOTENAME(@ColumnName) ' END'
EXEC sp_executesql @UpdateStmt, N'@OldValue NVARCHAR(MAX), @NewValue NVARCHAR(MAX)', @OldValue, @NewValue
END
SET @aExists = 0
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
END
-- SELECT * FROM #TempTable
--关闭且删除游标
CLOSE tableCursor
DEALLOCATE tableCursor第二种：
代码语言：javascript复制DECLARE @OldValue NVARCHAR(MAX) = '人员A-ID'
DECLARE @NewValue NVARCHAR(MAX) = '人员B-ID'
DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @UpdateStmt NVARCHAR(MAX)
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @aExists INT
DECLARE @SQLString NVARCHAR(1000)
--创建游标，获取库中所有(表名,列名)
DECLARE tableCursor CURSOR FOR
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.system_type_id IN (167, 175, 231, 239) -- 数据类型为char、varchar、nchar、nvarchar的列
-- CREATE TABLE #TempTable (
-- ID NVARCHAR(MAX),
-- CNAME NVARCHAR(MAX)
-- )
--
--获取游标中的行
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
--循环游标
WHILE @@FETCH_STATUS = 0
BEGIN
--用来排除某些表或者列
IF @TableName='TABLE-A' OR @ColumnName='COLUMN-A'
BEGIN
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
CONTINUE
END
--判断此表此列是否存在 值 等于 人员A-ID ，如果有替换值
SET @SqlQuery = 'SELECT @aExists=1 FROM ' QUOTENAME(@TableName) ' WHERE ' QUOTENAME(@ColumnName) ' = @OldValue'
EXEC sp_executesql @SqlQuery, N'@OldValue NVARCHAR(MAX), @aExists INT OUTPUT', @OldValue, @aExists OUTPUT
IF @aExists=1
BEGIN
--将相关的合并数据进行备份，备份表(bak_tran),此表结构应根据具体情况设计，以下只供参考。原本打算存在临时表（TempTable）
SET @SQLString = 'SELECT ID,''' @TableName ''',''' @ColumnName ''',''' @NewValue ''',''' @OldValue ''' FROM ' QUOTENAME(@TableName) ' WHERE ' QUOTENAME(@ColumnName) ' = @OldValue'
INSERT INTO [bak_tran](id,tname,cname,new,old) EXEC sp_executesql @SQLString,N'@OldValue NVARCHAR(MAX)',@OldValue
--将 人员A-ID 替换为 人以员B-ID
SET @UpdateStmt = 'UPDATE ' QUOTENAME(@TableName) ' SET ' QUOTENAME(@ColumnName) ' = replace( ' QUOTENAME(@ColumnName) ',@OldValue,@NewValue) '
EXEC sp_executesql @UpdateStmt, N'@OldValue NVARCHAR(MAX), @NewValue NVARCHAR(MAX)', @OldValue, @NewValue
END
SET @aExists = 0
FETCH NEXT FROM tableCursor INTO @TableName, @ColumnName
END
-- SELECT * FROM #TempTable
--关闭且删除游标
CLOSE tableCursor
DEALLOCATE tableCursor代码讲解
第一种和第二种的唯一差别是 48 行的 SQL 语句不同，一个是用 case 判断替换全值，一个是用 replace 函数部分替换。
首先，从sys.tables表查找全库的表和列，且赋值给游标。
然后，循环游标。
之后，判断某些表名和列名，用来排除一些特殊表或者列。
下一步，判断是否存在被替换值，若有则进行数据备份(为了安全起见)
最后，替换值，结尾删除游标
