sql - SQL 循环遍历 800 万条记录并更新它们
问题描述
我有一个包含大约 800 万条记录的审计表。我最近添加了两个新列,我需要使用一些规则/条件从现有列进行更新。基本上,最初,每当在表中更新 FK 时,它都会将新旧 FK id 存储到审计表中。例如
Table A
ID Name
1 First A
2 Second A
3 Third A
Table B
ID AID Name
1 1 First B
2 1 Second B
3 2 Third B
Audit
ID TableName FieldName OldValue NewValue
现在,如果我将表 B 的第一条记录从 1 1 First B 更新为 1 3 First B 那么审计表会将更改存储为
Audit
ID TableName FieldName OldValue NewValue
1 Table B AID 1 3
现在我更新了审计表来存储 FK 的实际文本值,即上述更改将存储为
Audit
ID TableName FieldName OldValue NewValue OldText NewText
1 Table B AID 1 3 First A Third A
问题是我已经有大约 800 万条记录需要创建新列。我写了下面的查询来做到这一点
declare @sql nvarchar(max);
declare @start int = 1
while @start <= 8000000
begin
select top 10000 @sql = COALESCE(@sql+'Update Audit set ','Update Audit set') +
isnull(' OldText = ('+ dbo.GetFKText(i.TableName, i.FieldName)+case when len(isnull(i.OldValue,'')) < 1 then null else i.OldValue end +'),',' OldText = OldValue, ') +
isnull(' NewText = ('+ dbo.GetFKText(i.TableName, i.FieldName)+case when len(isnull(i.NewValue,'')) < 1 then null else i.NewValue end +')',' NewText = NewValue ') +
' where AuditID = '+cast(i.AuditID as nvarchar(200))+' and lower(ltrim(rtrim(TableName))) <> ''audit'';'
from Audit i where i.AuditID >= @start
exec sp_executesql @sql
set @start = @start+10000;
end
获取文本函数(基本上我得到的列具有 name = (TableName)+'Name' 或 (TablName)+(SomeText)+'Name' 这只是我在所有表格中遵循的约定)
declare @res nvarchar(max)='';
declare @fn nvarchar(200);
declare @ttn nvarchar(200);
declare @tcn nvarchar(200);
SELECT top 1
@ttn = kcu.table_name
,@tcn = kcu.column_name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
Where ccu.TABLE_NAME = @TableName and ccu.COLUMN_NAME = @FieldName
if isnull(@ttn,'') != '' and ISNULL(@tcn,'') != ''
begin
select @fn= COLUMN_NAME
from (SELECT top 1 COLUMN_NAME ,
case when COLUMN_NAME like (@ttn+'Name') then 0
when COLUMN_NAME like (@ttn+'%Name') then 1
when COLUMN_NAME like (@ttn+'Code') then 2
when COLUMN_NAME like (@ttn+'%Code') then 3 else 4 end as CPriority
FROM JVO.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @ttn and (COLUMN_NAME like '%Name' or COLUMN_NAME like '%Code'
)
order by CPriority) as aa;
RETURN 'select '+@fn+' from '+@ttn+' where '+@tcn+' = ';
end
return null;
它工作但非常慢,它在 13 小时内更新了大约 100 万条记录。任何人都可以帮助改进此查询或建议更新它的替代方法。
谢谢
解决方案
推荐阅读
- fp-ts - 替换 fp-ts 中的数组元素
- python - 将for循环值存储到python中的列表中
- swift - 搜索期间未加载搜索 Tableview
- reactjs - 如何在 React 中使用带有 Typescript 的 ContextAPI 传递和设置状态?
- java - 重用从 Server 获取的 HttpConnection 用于 HttpClient
- python - 如何在 PyQt5 中的 QVideoWidget 中旋转视频
- flutter - Dart 客户端类型通用 REST Api 调用
- python - 根据另一个数组的值打印一个数组的值
- vue.js - 来自 Vue mixins 和 Vuex 的输出文件的大小
- python - 当我尝试查找像素颜色时,pyautogui 出现故障,但有时当我什至不更改代码时它也可以工作