...
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 versions of the same data. They have the same column names and the Types assigned to corresponding columns in each table allows the data to be automatically converted. The declared type name does not have to be the same (and even when it is, the same name may mean different things in different systems), but they have to describe generic numbers, strings, or timesif you link one database server to another you can copy both the definition and the contents of a table from one server to another using “SELECT INTO” or copy data from one table to another using “INSERT SELECT”.
However, things become more complicated if you are moving tables or data from one type of database to another. The requirement to migrate the Oracle ACS1 database to SQL Server produced a Powershell script that can be more generally used to synchronize information between databases and support Sandbox development and reporting.
Since our applications are written in Java, not use it? Powershell is based on the .NET framework, and while both Java and .NET have a similar plug in design for database drivers, .NET has full table operations that simply copying and migration.
The SQL for CREATE TABLE is much the same in all databases. The exact Types of columns may need adjustment because specific Type names mean different things in different systems. That is a manual process that you have to work out on your own. We assume you have generated an equivalent definition of each table in a target output system. The columns have the same name, and the types are similar enough that data can be automatically converted from one to the other. NUMERIC, DECIMAL, TINYINT, SMALLINT, INT, or BIGINT are different sizes of numbers, CHAR, VARCHAR, VARCHAR2, NVARCHAR are different character strings with different sizes and maybe character sets. Dates and timestamps will similarly convert even though different systems may use different starting dates and precisions.
The base version of the script has no special support for There is no automatic way to handle data types like Blobs, Clobs, XML, etc. If you have columns of these types, you may need to write special conversion rules anyway.
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 While we want to copy the exact data between production systems, when we are moving data to a TEST or developer Sandbox, we may want to audit or transform the contents. For testing, we may want to assign a single common dummy SSN or Date of Birth to everyone, since the actual values are not needed for testing .
The original use case was to migrate an Oracle database to SQL Server. The tool was first used to periodically copy the Oracle production data to a developer’s desktop Sandbox database. Later tables could be synchronized between Oracle and a Yale TEST or PROD SQL Server database.
However, a database has many tables for many applications. Different functions will migrate at different times. So while the migration stretches out over time, the and we then reduce the sensitivity of the copied tables.
During initial development the script can be used to periodically refresh the important tables from a source database to another. However, as we start to migrate application, different components will move at different times. The tool can synchronize some Oracle tables to SQL Server (to test new code) and some SQL Server tables back to Oracle (to support unconverted legacy applications and reports after the production application has moved to SQL Server).
...