PowerShell导入CSV至SQL Server时删除库中CSV已不存在的记录

核心实现思路 #

你现有代码用GSID + Source两个字段作为记录重复的判断依据，删除逻辑和判重逻辑保持一致即可：删除Lit_Hold_Err表中，GSID和Source的组合不在本次导入CSV所有记录的GSID+Source组合中的数据。
不要逐行遍历做删除，性能差且容易出错，推荐批量比对后一次性执行删除。根据CSV数据量大小，可以选两种实现方案：

• 方案1：CSV记录数小于1000条时，直接拼接SQL条件执行删除，代码简单
• 方案2：CSV记录数大于1000条时，用临时表+BulkCopy批量导入比对，无SQL长度限制，性能更高

完整实现代码 #

1. 基础导入与数据预处理 #

首先导入CSV并提取所有记录的唯一业务键，同时对CSV本身的重复记录做去重：

# 导入CSV文件
$CSVImport = Import-CSV $Global:ErrorReport
if (-not $CSVImport) {
    Write-Warning "CSV文件无有效数据，跳过同步"
    exit 0
}

# 提取CSV中所有记录的唯一业务键（和原有判重逻辑保持一致：GSID+Source）
$csvBusinessKeys = $CSVImport | ForEach-Object {
    [PSCustomObject]@{
        GSID = $_.GSID.Trim()
        Source = $_.Source.Trim()
    }
} | Group-Object GSID, Source | ForEach-Object { $_.Group[0] }

2. 冗余记录删除逻辑 #

方案1：小数据量简单实现（<1000行）

直接拼接比对条件执行删除，无需额外依赖：

# 拼接CSV侧的键值匹配条件
$keyMatchClauses = $csvBusinessKeys | ForEach-Object {
    "(GSID = '$($_.GSID -replace "'","''")' AND Source = '$($_.Source -replace "'","''")')"
}

$deleteQuery = @"
DELETE FROM Lit_Hold_Err
WHERE NOT EXISTS (
    SELECT 1 FROM (VALUES
        $($keyMatchClauses -join ",`n        ")
    ) AS csvKeys(GSID, Source)
    WHERE Lit_Hold_Err.GSID = csvKeys.GSID AND Lit_Hold_Err.Source = csvKeys.Source
)
"@

# 执行删除
Invoke-Sqlcmd -Query $deleteQuery -ServerInstance $Global:Server -Database $Global:Database -QueryTimeout 120

方案2：大数据量稳妥实现（>1000行）

通过临时表+BulkCopy批量导入键值做关联删除，避免SQL语句过长报错，性能提升明显：

$connectionString = "Server=$Global:Server;Database=$Global:Database;Integrated Security=True;"
# 若使用SQL账号认证，替换为上述连接字符串为：
# $connectionString = "Server=$Global:Server;Database=$Global:Database;User ID=账号;Password=密码;"

using ($sqlConn = New-Object System.Data.SqlClient.SqlConnection($connectionString)) {
    $sqlConn.Open()

    # 创建临时表存储CSV侧的业务键
    $createTempCmd = $sqlConn.CreateCommand()
    $createTempCmd.CommandText = @"
CREATE TABLE #TempCsvKeys (
    GSID NVARCHAR(100) NOT NULL,
    Source NVARCHAR(500) NOT NULL,
    PRIMARY KEY CLUSTERED (GSID, Source)
)
"@
    $createTempCmd.ExecuteNonQuery() | Out-Null

    # 将业务键批量写入临时表
    $dataTable = New-Object System.Data.DataTable
    $dataTable.Columns.Add("GSID", [string]) | Out-Null
    $dataTable.Columns.Add("Source", [string]) | Out-Null
    foreach ($key in $csvBusinessKeys) {
        $row = $dataTable.NewRow()
        $row["GSID"] = $key.GSID
        $row["Source"] = $key.Source
        $dataTable.Rows.Add($row)
    }
    $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
    $bulkCopy.DestinationTableName = "#TempCsvKeys"
    $bulkCopy.WriteToServer($dataTable)
    $bulkCopy.Close()

    # 关联临时表删除冗余数据
    $deleteCmd = $sqlConn.CreateCommand()
    $deleteCmd.CommandText = @"
DELETE FROM Lit_Hold_Err
WHERE NOT EXISTS (
    SELECT 1 FROM #TempCsvKeys k
    WHERE Lit_Hold_Err.GSID = k.GSID AND Lit_Hold_Err.Source = k.Source
)
DROP TABLE #TempCsvKeys
"@
    $deleteCmd.ExecuteNonQuery() | Out-Null
}

3. 新增记录插入逻辑（修正原代码单引号转义不全问题） #

原代码仅对Source字段做单引号转义，其他字段包含单引号时会触发SQL语法错误，修正后逻辑如下：

ForEach ($CSVLine in $CSVImport) {
    # 所有字符串字段统一做单引号转义
    $hold = $CSVLine.Hold -replace "'","''"
    $gsid = $CSVLine.GSID -replace "'","''"
    $source = $CSVLine.Source -replace "'","''"
    $type = $CSVLine.TYPE -replace "'","''"
    $msg = $CSVLine.Message -replace "'","''"
    $createdTime = $CSVLine.Time -replace "'","''"

    $insertQuery = @"
IF NOT EXISTS (SELECT 1 FROM Lit_Hold_Err 
    WHERE GSID = '$gsid' AND Source = '$source')
BEGIN
    INSERT INTO Lit_Hold_Err (Hold, GSID, Source, Error, Type,Date)
    VALUES('$hold', '$gsid', '$source','$msg','$type', '$createdTime') 
END
"@
    Invoke-Sqlcmd -Query $insertQuery -ServerInstance $Global:Server -Database $Global:Database
}

注意事项 #

• 正式执行删除前，先把DELETE FROM Lit_Hold_Err替换为SELECT * FROM Lit_Hold_Err执行，确认查询结果是确实需要删除的冗余记录，避免误删
• 生产环境建议用参数化查询或SqlBulkCopy全量导入的方式替代SQL字符串拼接，避免SQL注入风险
• 如果CSV中时间字段格式不统一，插入前建议统一转成yyyy-MM-dd HH:mm:ss的SQL兼容格式，避免插入失败

内容的提问来源于stack exchange，提问作者aase movie