Instant Oracle Database and PowerShell How-to - Instant Oracle Database and PowerShell How-to - page 24

4. Register an event to be notiied as record batches are inserted:
Register-ObjectEvent -InputObject $bulkCopy -EventName
OracleRowsCopied -SourceIdentifier BatchInserted `
-MessageData $countryDT.Rows.Count -Action {
$rowsCopied = $($event.sourceEventArgs.RowsCopied)
$msg = "Inserted $rowsCopied records so far"
$percentComplete = ($rowsCopied / $($event.MessageData) *
100)
Write-Host $msg
Write-Progress -Activity "Batch Insert" -Status $msg
-PercentComplete $percentComplete
} | Out-Null

5. Call WriteToServer to start the import and time the duration:

$sw = [System.Diagnostics.StopWatch]::StartNew()
$bulkCopy.WriteToServer($countryDT)
$sw.Stop()
("Inserted {0} records total in {1:#0.000} seconds" -f $countryDT.
Rows.Count, $sw.Elapsed.TotalSeconds)

6. Perform teardown:

Unregister-Event -SourceIdentifier BatchInserted
$bulkCopy.Dispose()
$conn.Close()
$conn.Dispose()

7. Run the script. The sample output is as follows:

Inserted 50 records so far
Inserted 100 records so far
Inserted 150 records so far
Inserted 200 records so far
Inserted 210 records total in 0.052 seconds

How it works...

First a DataSet is created and loaded using the ReadXml method and the countries.
xml ile in the current directory. This XML ile is a slimmed down version from http://
madskristensen.net/post/XML-country-list.aspx and was not created with a
DataSet but the DataSet infers the schema from the data. A REGION_ID column is added
since the source data doesn't contain this value but we want it in the COUNTRIES table.
A default value of 5 ensures each row in the table gets this value.