sqlBulkCopy

Voici un exemple de source powershell qui permet de copier une table vers une autre. La table de destination sera vidé de son contenue et le continue de la table source sera copié.

$SrcServer=  "ServeurSource"
$SrcDatabase = "BaseSource"
$SrcTable = "TableSource"
$DestServer=  "ServeurDestination"
$DestDatabase = "BaseDestination"
$DestTable = "TableDestination"
$auth = "Active Directory Integrated"

$SrcConnStr = "Server = $SrcServer; Database = $SrcDatabase; Authentication = $auth; TrustServerCertificate = True;"
$SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)


$CmdText = “SELECT * FROM “ + $SrcTable

$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn) 
$SrcConn.Open()

$SqlReader = $SqlCommand.ExecuteReader()

$DestConnStr = "Server = $DestServer; Database = $DestDatabase; Authentication = $auth; TrustServerCertificate = True;"
$DestConn  = New-Object System.Data.SqlClient.SQLConnection($DestConnStr)

# Empty destination table
$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.connection = $DestConn

$strsql = "TRUNCATE TABLE " +  $DestTable
$sqlcmd = new-object "System.data.sqlclient.sqlcommand"
$sqlcmd.CommandText = $strsql
$adapter = New-Object system.data.sqlclient.sqldataadapter ($sqlcmd.CommandText, $DestConn)
$set = New-Object system.data.dataset
$adapter.Fill($set)

# Bulkcopy to the destination table
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $DestTable
$bulkCopy.WriteToServer($sqlReader)

$SqlReader.close()
$SrcConn.Close()
$SrcConn.Dispose()
$bulkCopy.Close()