将数据集加载到 SQL 脚本（在 powershell 中）

Question

我想从一台服务器上的 SQL Server 数据库中提取大型数据集，然后将该数据集包含在另一台服务器上的另一个数据库中。由于链接服务器不是一个选项，我尝试了 powershell 脚本，并且想要类似的内容：

$connection = New-Object System.Data.SqlClient.SqlConnection
$command = New-Object System.Data.SqlClient.SqlCommand
... etc for SqlDataAdapter as well
$adapter.Fill($dataset1)

$connection2 = ... (to a database on another server)
$command2 = ...
Load $dataset1 into $command2
$command2.Fill($dataset2)

如何将数据集 1 加载到命令 2 中？

Answer 1

您确定要将整个数据集保存在内存中吗？

如果您只想将查询结果复制到另一个表，最好使用数据读取器以避免内存不足异常。

# cf. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

$SourceConnectionString      = "Data Source=localhost;Initial Catalog=source_db;Integrated Security=True"
$DestinationConnectionString = "Data Source=localhost;Initial Catalog=Destination_db;Integrated Security=True"

$tableName = "MyTable"
$sql = "select * FROM $tableName"

$sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($ConnectionString)
$sourceConnection.open()
$commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
#$commandSourceData .CommandTimeout = '300'

ps 'powershell_ise'
$reader = $commandSourceData.ExecuteReader()
ps 'powershell_ise'


try
{
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $DestinationConnectionString
    $bulkCopy.DestinationTableName = $tableName
    $bulkCopy.BatchSize = 5000
    $bulkCopy.BulkCopyTimeout = 0
    $bulkCopy.WriteToServer($reader)
}
catch
{
    $ex = $_.Exception
    Write-Host "Write-DataTable$($connectionName):$ex.Message"
}
finally
{
    $reader.close()
}

编辑：

阅读 Mike 的评论后，PowerShell 可能会展开 datareader 对象，我重试了代码替换。

$sql = "select * FROM $tableName"

它

$sql = "select * FROM $tableName union all select * FROM $tableName union all select * FROM $tableName union all select * FROM $tableName union all select * FROM $tableName "

仍然有效，并且我没有出现内存不足的异常，直到

$reader = $commandSourceData.ExecuteReader()

我观察到问题，我没有理由尝试迈克的变体。

第二次编辑：

添加来修改代码，

ps 'powershell_ise'

我通过在之前和之后

$reader = $commandSourceData.ExecuteReader()

但我没有观察到内存使用情况有任何变化，因此我得出结论，Mike 关于 PowerShell 展开 datareader 对象的假设不适用。

Answer 2

您是否考虑过 SQLBulkCopy 类？ 这里是一个 powershell 函数，它使用它将数据从一个数据集推送到另一个 SQL 连接。