使用PowerShell向SQL Server插入日期及导入CSV数据至数据库问询

嘿，我看你现在用PowerShell逐行插入CSV数据到SQL Server，先不说性能问题，这种直接拼接SQL语句的方式非常容易触发SQL注入，而且大批量数据的话速度会慢到离谱。给你几个更靠谱的方案：

1. 使用SqlBulkCopy（最推荐，性能拉满） #

这是批量插入的神器，直接把CSV数据转成DataTable后一次性批量写入，比逐行插入快N倍，还能避免SQL注入风险。代码示例如下：

# 加载必要的.NET程序集
Add-Type -AssemblyName System.Data.SqlClient
Add-Type -AssemblyName System.Data

# 读取CSV文件到DataTable
$csvPath = "你的CSV文件路径.csv"
$dt = New-Object System.Data.DataTable
$reader = New-Object System.IO.StreamReader($csvPath)

# 处理表头
$headers = $reader.ReadLine().Split(',') | ForEach-Object { $_.Trim() }
foreach ($header in $headers) {
    $dt.Columns.Add($header) | Out-Null
}

# 处理每行数据
while (($line = $reader.ReadLine()) -ne $null) {
    $row = $dt.NewRow()
    $values = $line.Split(',')
    for ($i=0; $i -lt $headers.Count; $i++) {
        $row[$headers[$i]] = $values[$i].Trim()
    }
    $dt.Rows.Add($row)
}
$reader.Close()

# 批量写入SQL Server
$connectionString = "Server=你的服务器名;Database=你的数据库名;Integrated Security=True;"
$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bulkCopy.DestinationTableName = "Offerings"

# 如果CSV列名和数据库列名不完全匹配，需要手动映射列（列名一致的话可以跳过这步）
# foreach ($col in $dt.Columns) {
#     $bulkCopy.ColumnMappings.Add($col.ColumnName, $col.ColumnName) | Out-Null
# }

$bulkCopy.WriteToServer($dt)
$bulkCopy.Close()

2. 使用参数化查询（如果必须逐行插入的话） #

要是因为某些限制不能用批量插入，那绝对要改用参数化查询，绝对不能直接拼接SQL字符串。示例代码：

Add-Type -AssemblyName System.Data.SqlClient

$connectionString = "Server=你的服务器名;Database=你的数据库名;Integrated Security=True;"
$csvPath = "你的CSV文件路径.csv"
$data = Import-Csv -Path $csvPath

# 建立数据库连接
$conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$conn.Open()

# 定义带参数的SQL语句
$sql = @"
INSERT INTO Offerings (RATING, PARVALUE, DESCRIPTION, COUPON, MATURITY, DATEDDATE, CALLDATE, FIRSTCOUPON, CUSIP, PRICEYIELD, COM)
VALUES (@Rating, @ParValue, @Description, @Coupon, @Maturity, @DatedDate, @CallDate, @FirstCoupon, @Cusip, @PriceYield, @Com)
"@

$cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $conn)
# 提前定义参数（根据数据库字段类型调整SqlDbType）
$cmd.Parameters.Add("@Rating", [System.Data.SqlDbType]VarChar, 50)
$cmd.Parameters.Add("@ParValue", [System.Data.SqlDbType]Decimal)
$cmd.Parameters.Add("@Description", [System.Data.SqlDbType]VarChar, 255)
$cmd.Parameters.Add("@Coupon", [System.Data.SqlDbType]Decimal)
$cmd.Parameters.Add("@Maturity", [System.Data.SqlDbType]Date)
$cmd.Parameters.Add("@DatedDate", [System.Data.SqlDbType]Date)
$cmd.Parameters.Add("@CallDate", [System.Data.SqlDbType]Date)
$cmd.Parameters.Add("@FirstCoupon", [System.Data.SqlDbType]Date)
$cmd.Parameters.Add("@Cusip", [System.Data.SqlDbType]VarChar, 20)
$cmd.Parameters.Add("@PriceYield", [System.Data.SqlDbType]Decimal)
$cmd.Parameters.Add("@Com", [System.Data.SqlDbType]VarChar, 50)

# 逐行赋值并执行
foreach ($row in $data) {
    $cmd.Parameters["@Rating"].Value = $row.rating.Trim()
    $cmd.Parameters["@ParValue"].Value = $row."PAR VALUE".Trim()
    $cmd.Parameters["@Description"].Value = $row.DESCRIPTION.Trim()
    $cmd.Parameters["@Coupon"].Value = $row.COUPON.Trim()
    $cmd.Parameters["@Maturity"].Value = $row.MATURITY.Trim()
    $cmd.Parameters["@DatedDate"].Value = $row.DATEDDATE.Trim()
    $cmd.Parameters["@CallDate"].Value = $row.CALLDATE.Trim()
    $cmd.Parameters["@FirstCoupon"].Value = $row.FIRSTCOUPON.Trim()
    $cmd.Parameters["@Cusip"].Value = $row.CUSIP.Trim()
    $cmd.Parameters["@PriceYield"].Value = $row.PRICEYIELD.Trim()
    $cmd.Parameters["@Com"].Value = $row.COM.Trim()

    $cmd.ExecuteNonQuery() | Out-Null
}

$conn.Close()

额外小提示 #

• 注意CSV里的日期格式要和SQL Server兼容，必要时可以用[DateTime]::ParseExact()手动转换格式
• 如果CSV字段里包含逗号（比如描述字段），Import-Csv可能解析出错，建议用第一种DataTable+StreamReader的方式处理，或者指定正确的分隔符
• 批量插入前可以临时禁用表上的非聚集索引，插入完成后再重建，能进一步提升写入速度

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