使用虚拟数据SQL查询更新敏感列。

I got a table that includes forename and surname that needs changing with random data but readable for testing purpose. So I created another table with random forenames and surnames in a different database. I would like to update the test database with the random forenames and surnames I created. Also I want to rearrange the names updated just in case so I don't want it to be updated in order if you understand what I mean.

我得到了一个表，其中包括需要随随机数据而变化的名字和姓氏，但用于测试目的是可读的。因此，我在另一个数据库中创建了另一个带有随机名和姓氏的表。我想用我创建的随机名字和姓氏来更新测试数据库。我还想重新排列这些名称，以防万一，如果你明白我的意思，我不希望它被更新。

I got this query

我得到了这个查询

update DB1.table 
set Forename = (???? from DB2.table (Forename)??????)
where Forename is not null

1 个解决方案

#1

Declare @Id int

DECLARE db_cursor CURSOR FOR  
SELECT Id 
FROM DB1.dbo.table 
WHERE Forename is not null

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @id   

WHILE @@FETCH_STATUS = 0   
BEGIN   
        update DB1.dbo.table 
        set Forename = (select top(1) Forename from DB2.dbo.table order by newid())
        where Id = @id

       FETCH NEXT FROM db_cursor INTO @id   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Will update each forename with from table1 with a random forename from table2

将从table1中随机取一个名字，从table2中更新每个名字吗?

You could then do the same for surnames, which would jumble up the forename/surname from table 2, which I assume is what you mean by the last line...

然后你可以对姓氏做同样的事情，这将混淆表2的名字/姓氏，我想这就是你在最后一行的意思……

#1

Declare @Id int

DECLARE db_cursor CURSOR FOR  
SELECT Id 
FROM DB1.dbo.table 
WHERE Forename is not null

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @id   

WHILE @@FETCH_STATUS = 0   
BEGIN   
        update DB1.dbo.table 
        set Forename = (select top(1) Forename from DB2.dbo.table order by newid())
        where Id = @id

       FETCH NEXT FROM db_cursor INTO @id   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor