更新时间:2023-01-19 23:26:31
我有两种方法可以解决这个问题:
There are two ways I would approach this:
BCP.exe
SQL Server提供了命令行实用程序 bcp 来批量导入数据.您可以简单地将bcp执行合并到Powershell脚本或窗口中以加载csv数据. 示例:
SQL Server provides the command line utility bcp to bulk import data. You could simply incorporate the bcp execution into your Powershell script or window to load the csv data. Example:
$loadfile = "C:\datafile\loadthis.csv"
bcp pity.dbo.foo in $loadfile -T -c -t","
使用.NET
您也可以在Powershell中使用.NET库,但这是一个比较棘手的建议.首先,获取 Out-DataTable 和 Write-DataTable 脚本,这将使您的生活更加充实,容易得多.然后,您可以执行以下操作:
You could also use the .NET libraries in Powershell, but this is a much trickier proposition. First, get the Out-DataTable and Write-DataTable scripts by Chad Miller, which will make your life much, much easier. Then you could do the following:
$dt = Import-Csv -Path "C:\datafile\loadthis.csv" | Out-DataTable
Write-DataTable -ServerInstance "localhost" -Database "pity" -TableName "foo" -Data $dt
These and other solutions can be found in detail in this blog post.