Elevator Pitch
There are many ways of migrating data between databases, even between different database vendors. PowerShell scripts can be a useful method, and if the target database supports bulk copy classes (for example Oracle and Microsoft SQL Server) it can be fast as well.
Description
There are countless ways and tools to migrate data – choosing often depends on the complexity of the situation, know-how, and how much code must be developed. For many less- to medium-complex cases, using PowerShell with .NET classes and libraries can be a good compromise for a relatively simple solution with not too much code requirement with the added benefit of being quite fast for some target databases (like Oracle and Microsoft SQL Server.)
The .NET managed data access classes for Oracle and SQL Server support bulk copy inserts of data, improving migration speed a lot. The presentation shows how to script this in PowerShell, accomplishing a simpler solution than having to build for example a C# program. Differences between PowerShell Core (PowerShell 7) and the classic Windows PowerShell (PowerShell 5) will be covered.
What makes these .NET classes flexible is the capability of accepting many types of source data – as long as you can create a DataReader or DataTable object, you’re good to go. It can be almost any OLEDB or ODBC connection, or dedicated database connection classes, or even flat files if you have a .NET class to parse it into a DataTable.
You can script the details on your own or you can work with existing PowerShell libraries like SimplySql that provides ready-to-use functionality. SimplySql also has the advantage that you have a choice of target databases besides Oracle and SQL Server – it can migrate to PostgreSQL, MySql and SQLLite as well, just using conventional inserts instead of bulk copy.
I’ll present and demo both using hand-built code as well as SimplySql, migrating data both to and from Oracle. I’ll highlight the small differences and caveats I’ve found in the various situations where I used this migration method, including differences whether the target tables are pre-created or has to be created as part of the process.
Notes
These .NET classes and PowerShell libraries are fairly powerful and relatively easy to use. They are not for every case of data migration, but for many of the not overly complex use cases, they can be a good choice.
I have used these successfully in several cases, migrating from legacy systems to Oracle with an ODBC source, migrating from Oracle to MS-SQL with an OLEDB source, and migrating from Oracle to PostgreSQL with Oracle ManagedDataAccess driver as source. I’ve found it to be reasonably easy to script, but I’ll share the small tips&tricks I discovered along the way while developing these scripts, like for example dealing with datatype differences.