Content Comparison

...

. .\CopyTableRows.ps1

# Connect to an Oracle input database. For protection we separate the Userid (who should have read-only access) from the password
$inConnectionString = 'User Id={0};Password={1};Data Source=server.its.yale.edu/xxx' -f 'inuser', 'inpassword'
$inconn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($inConnectionString)
$inconn.Open()   # Get a backing connection from the Oracle pool manager to reactivate the OracleConnection object

# Connect to a SqlSrv output database using the current Windows login as the credential
$outconn = New-Object System.Data.SqlClient.SqlConnection('Server=localhost;Integrated Security=true;Database=xxx;')
$outconn.Open()

Copy-DbTable -InputTableName 'EXAMPLE.TABLE' -InConn $inconn -OutConn $outconn -truncate

$inconn.Close()
$outconn.Close()

Considerations

Performance

You will see several different blocks of code in the script that do the same thing different ways. After measuring the elapsed time for each approach, it appears that there is no real optimization and all approaches take the same amount of time. BulkCopy is so highly optimized that truncating the old data and copying the entire table is typically faster than comparing new and old data and only updating changed rows. Of course, if you have a table with a million rows but only three rows have been changed, then this may be a special case, but in most normal cases the -truncate full data replacement is better or so close to the same as to make no meaningful difference.

Primary Key

In order to compare the input and output rows, the input and output tables must have Primary Keys. If you use the -truncate option and empty the output table and replace all the rows, then there is no key requirement.

Copy-History

Important “base” tables may have an associated History table. When any row is changed or deleted, the previous data in the row is copied to a new row at the end of the corresponding History table. This can happen automatically if there is a Trigger on the base table that is called with each UPDATE or DELETE.

The best practice is for rows in the History table to have an “identity” column, where a new sequential integer is generated each time a row is added. However, we have found that existing Oracle applications may not have any unique row identifier. It appears that in both Oracle and SQL Server new rows are appended to “the end of the table” and when reading rows, they are by default returned chronologically. This is regarded as bad practice in SQL textbooks.

If you use -truncate then you can treat history tables like any other table.

The Copy-History function provides experimental support for identifying the new records added onto the end of an Oracle history table and appending copies of just those records onto the end of a SQL Server history table. The copy works only in the one direction, because only Oracle has a SQL operation to skip over the old rows.

To copy a History table from SQL Server to Oracle, you must specify -truncate and copy the whole table from the beginning.

Earliest DATE

While both Oracle and SQL Server support DATE values going back millennia, the .NET SqlDateTime class has a minimum date of 1/1/1753. This tripped us up because a few Yale tables set unknown dates to the day that Yale was founded, Oct 5, 1701. That value could not be converted by Microsoft utilities and cannot be copied by this script. To get around this, a custom SELECT statement is created in the calling program and is passed with the optional parameter -SelectSQL. This SELECT statement contains expressions of the form:

...