Script in Git: https://git.yale.edu/gilbert/DBScripts/blob/main/CopyTableRows.ps1
Microsoft has a bcp.exe program to copy a table from one SQL Server database to another, and there are SQL statements to SELECT INTO a new table or INSERT from a SELECT that will bulk copy data and even replicate table column definitions. However, the documentation makes it clear that if you want to move data from another type of database (like Oracle) you need some custom programming.
Java has its JDBC standard into which you plug drivers for various systems. .NET has essentially the same idea. However, while Java works on a row by row basis, .NET has some full table read and write operations that speed things up and transfer responsibility from the programmer to the drivers themselves. The simplest language in which to configure a table by table copy is Powershell.
While some Powershell programming was involved in writing the script, and may be needed to customize it if you require more than the basic operation, in most cases you just have to load the script and then write one copy command per table.
The input and output tables are assumed to be roughly identical. They would have the same column names and types that allow the data to be automatically converted. Specifically, the database systems and their client drivers will automatically convert any type of number to any other type of number big enough to hold the largest data value. Columns with types like NUMERIC, DECIMAL, TINYINT, SMALLINT, INT, or BIGINT are compatible with each other, and different types of character strings (CHAR, VARCHAR, VARCHAR2, NVARCHAR) can be converted automatically although you may want to explicitly TRIM or declare character sets in the process. Dates and timestamps will similarly convert even though different systems may use different starting dates and precisions.
In most cases the script can simply do a SELECT * FROM the input table and write the data to the output table. If you have special requirements, you can provide a custom SELECT statement. For example, in the special case where you are copying data from a production database to a test system (especially a developer’s Sandbox) you may want to avoid copying the real values of any sensitive information. You can then specify a SELECT that gives everyone in the output table the same Social Security Number or Date of Birth if the real values are not needed for testing.
The tool was built to migrate an Oracle database to SQL Server. Initially the Oracle data is copied to a development Sandbox. Later it is copied to pre-production. As the system rolls out, different tables may migrate at different times. Some authoritative Oracle tables will be copied to shadow SQL Servers table to support testing, while other already migrated SQL Server tables will be copied back to Oracle to support legacy applications or reports that have not yet been converted.
The script can also be used to capture a whole database at points in time, then compare two snapshots to report what changed over time.
The base version of the script has no special support for data types like Blobs, Clobs, XML, etc. If you have columns of these types, you may need to write special conversion rules anyway.
Read and Write entire Tables
.NET defines some abstract classes of objects that perform generic functions. Oracle and SQL Server provide drivers that implement these template classes.
Two .NET templates are the DataAdapter and BulkCopy.
A DataAdapter will execute a SELECT and read the entire result into memory. A program can then change specific values in the collection of Row objects in memory. The objects track what has been changed. A single operation tells the DataAdapter to generate whatever INSERT, UPDATE, and DELETE operations are needed to transfer the changes previously made in memory to the database table.
If you give the DataAdapter a “SELECT *” statement, it will read metadata from the database and discover the names and types of each column. It then creates in memory native types for the client system that can hold any data that might be stored in that type of database column.
BulkCopy inserts new rows into a table, but it is optimized to transfer the data in a single operation rather than doing a sequence of INSERT statements each of which has to be sent and then the return has to be checked. It can add new data, or if the table has been emptied it can load the table with entirely new data. Triggers and Constraints are also disabled or optimized.
.NET also defines templates for a DataSet, which is a generic collection of DataTables, which are generic collections of DataRows. However, while the DataAdapter and BulkCopy are implemented by the database drivers from the vendors (Oracle or SQL Server for example), the DataSet, DataTable, and DataRow are .NET classes independent of and generic to all databases.
An example of the normal use of the script to copy all data in an Oracle table to a SQL Server table (which may have an older copy of the data in it) is to
Connect to the two databases with userids and password. The Oracle userid has to be able to read the table and the SQL Server userid has to be able to write to the table.
Have Oracle create a DataAdapter for the input table based on a “SELECT *”. This discovers the definition of the input table in the Oracle database including its column names and types.
Have SQL Server create a BulkCopy for the output table.
Create an empy DataSet object in memory.
Call the DataAdapter.fill() method passing the empty DataSet. This reads the table into memory generating one DataTable with DataRows containing the contents of the table. Oracle decides what native binary data types to use to hold each of the columns.
Start a Transaction on the output database.
TRUNCATE the output table to delete all the old data.
Call BulkCopy.WriteToServer() passing the DataTable that Oracle generated. SQL Server converts whatever data Oracle put in memory to whatever type it needs to send the data to the database and repopulate its contents.
Commit the Transaction to swap the old and new version of the output table.
You can now close the connections or copy another table.
There are alternate Copy-Table functions that combine steps 2 through 9. If the tables have a unique key, then there is an option to read both the input and output tables into memory, compare the rows to see which ones are different, and only make the specific changes needed. However, it turns out that copying all the data is usually as fast or faster than comparing the tables. There is also a special function to append new data on the end of a table that doesn’t have a primary key.
If you need some special transformation of the data, the best option is to create a string containing a specific SELECT statement using SQL functions to transform the data (SELECT … NULL AS SOCIAL_SECURITY_NUMBER …) and pass it to the Powershell command. If you need to do a more elaborate transformation, then you have to write some Powershell loop. Examples are provided in the code.
However, in the normal case the Powershell script does not need any loops and does not look at individual rows let alone individual cells. The copy is done by complied code provided by Oracle and SQL Server. They choose the .NET data types and do any conversion needed. The script performs so little work itself that the copy runs as fast in Powershell as it would in any complied language.
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.
. .\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
Suppose you are copying a table every night and typically it only has a few dozen rows changed since the last copy. Is it better to compare the two tables and only make the necessary changes?
It was necessary to write the script and try both a full copy and an incremental update. It turns out that there is very little difference between the time it takes to perform each approach. It takes the same amount of time to read a whole table and write a whole table as it does to read the table twice from two sources.
Therefore, the choice may depend on special factors about the table. Are there constraints, indexes, triggers, or something else that makes it better to make the smallest number of changes possible? If not, then the full copy works as well as anything else.
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:
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.