我如何优化我的插入sql查询，包含没有唯一值的字段?

Question

我有一个分段表，它的所有字段都是varchar，该表中没有唯一值/id。我正在转换和插入另一个表与正确的数据类型，但查询运行约6小时写1500万条记录。我如何优化我的插入sql查询，因为字段不包含唯一的值?

我的代码

INSERT INTO reportingTable
(      
[activityYear]
,[stateCode]
,[countyCode]
,[loanAmount]
,[loanToValue]      
)
SELECT 
CAST(activityYear AS INT) AS activityYear,
CAST(stateCode AS varchar(2)) AS stateCode,
CAST((CASE WHEN ISNUMERIC(countyCode) = 0 then '-99999999' else countyCode END ) AS INT) AS countyCode,
TRY_CAST(TRY_CAST(loanAmount as float) as BIGINT) AS loanAmount,
TRY_CAST((CASE WHEN loanToValueRatio='Exempt' THEN '-88888888' WHEN ISNUMERIC(loanToValue) = 0 THEN '-99999999' else TRY_CAST(loanToValue as FLOAT) END ) AS NUMERIC(14,2)) AS loanToValue,
FROM stagingTable

示例数据

tbody> <<tr>

activityYear	stateCode	countryCode	loanAmount	loanToValue
2018	纽约	NA	20000	NA
2018	数控	36047	105000	的
2019	IA	42003	435000	10.05
2019	PA	36087	305000	74
2020	CA	6095	65000	90
2020	莫	12115	45000	80
2020	纽约	NA	105000	65.11
2021	数控	36047	65000	95
2021	IA	42003	65000	85
2021	IA	19061	55000	NA
2021	纽约	19153	225000	55.73
2021	纽约	19153	300000	60
2021	数控	36047	600000	80
2021	数控	6085	10000	的

您可以使用"SQL Server导入和导出向导";工具输出您的数据作为平面文件。然后在目标表中输入bulk insert。另一个解决方案是在SSIS中使用ETL进程工具。