Script in Git: https://git.yale.edu/gilbert/DBScripts/blob/main/CopyTableRows.ps1
As part of the migration from Oracle ACS1 to SQL Server, it was necessary to build a tool that could quickly copy the content of ACS1 tables to converted versions of the same table on SQL Server. The Oracle and SQL Server tables have the same names, column names, and the same or at least a compatible type. There are ways to copy Oracle to Oracle and SQL Server to SQL Server, but you have to write a program to copy tables across different databases. While Talend does data copying in production, a developer needs something they can run on demand. The same program can be run in a way to compare the two tables and report differences.
...
The script can be modified to add custom conversions if the default behavior is inadequate. However, before writing a lot of code, you can write a custom SELECT that creates the input DataAdater, and in the SQL you can CAST or CONVERT the data in certain columns to a more appropriate type. This is simpler and runs much faster than Powershell loops.
Use
Download the script from Git.
The input and output databases must have the same table name, with the same column names and compatible types.
Create a connection to the input and output databases. Then call the functions in the script to copy tables.
The following code truncates (empties) EXAMPLE.TABLE in the output database, then replaces its contents with the rows of the same table in the input database.
| Code Block |
|---|
. .\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
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:
GREATEST(CHANGE_EMAIL_ADDRESS_DATE,DATE '1776-07-04') AS CHANGE_EMAIL_ADDRESS_DATE
So that the earliest date allowed is July 4, 1776, but you could choose any other date after 1753 if you prefer.