Eksempel
Eksemplet (PowerShell) tager udgangspunkt i at en samling af SQL Server instansregistreringer kopieres fra et kildesystem til SQLAdmin staging området.
Som parameter til SqlBulkCopy objektet benyttes i dette tilfælde et DataTable objekt. Jeg har ikke nogen holdning til anvendelse af DataTable frem for DataReader.
Ud over ADO.NET klassen SqlBulkCopy benytter scriptet ADO.NET klassen SqlCommand til at tømme måltabellen.
$cnnStrSource = "server=yourServer\yourInstance;integrated security=true;database=yourDb"
$cnnStrTarget = "server=myServer.sqladmin.dk\myInstance;integrated security=true;database=D0000_SQLAdmin"
$tblTarget = "[dbo].[tbl0410_yourDb_Instances]"
$SQLquery = "SELECT DISTINCT * FROM [dbo].[Instances] WHERE [Access]='True';"
$SQLTruncateTarget = "TRUNCATE TABLE " + $tblTarget + ";"
[reflection.assembly]::LoadWithPartialName( "System.Data.SqlClient" )
# Source
$cnnSource = New-Object "System.Data.SqlClient.SqlConnection" $cnnStrSource
$cnnSource.Open()
$dataSet = New-Object "System.Data.DataSet"
$dataAdpt = New-Object "System.Data.SqlClient.SqlDataAdapter" ( $SQLquery, $cnnStrSource )
$rows = $dataAdpt.Fill( $dataSet )
$cnnSource.Close()
#Target
$cnnTarget = New-Object "System.Data.SqlClient.SqlConnection" $cnnStrTarget
$cnnTarget.Open()
#Truncate target staging table
$cmdTruncate = New-Object "System.Data.SqlClient.SqlCommand"
$cmdTruncate.Connection = $cnnTarget
$cmdTruncate.CommandText = $SQLTruncateTarget
$ReturnValue = $cmdTruncate.ExecuteNonQuery()
$cmdTruncate = $null
#Bulk Copy
$bulkcopy = New-Object "System.Data.SqlClient.SqlBulkCopy" ( $cnnStrTarget )
$bulkcopy.DestinationTableName = $tblTarget
$bulkcopy.WriteToServer( $dataSet.Tables[0] )
$cnnTarget.Close()