I was recently asked to review a migration plan from SQL Server to Oracle. The actual export and import of the table data was broken into two separate tasks and the time projected was (in my opinion) overly long. I did not know the exact details of what steps were being performed during the export and import, but I immediately thought about improving the process by using the SQL Server Import and Export Wizard.
I first encountered the SQL Server Import and Export Wizard utility in the early days of working with SQL Server versions 7 and 2000 and before you ask, yes I am old. I attended the Microsoft launch event for SQL Server 7 while living in Denver and even got the t-shirt!
Anyway, I was impressed with this tool even this early in its history and the fact that such a capable tool was bundled with SQL Server at no additional charge. This is a practice that continues today.
The use of this tool is well-documented (https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard?view=sql-server-2017), but I will give a brief overview in this post.
The SQL Server Import and Export Wizard can be accessed in a couple of ways. The first is directly though the Start menu in Windows:
It is interesting to note that both 32 and 64-bit versions are available in this manner. Another method of access is directly from the SQL Server Management Studio when right-clicking on a database, going to the tasks sub-menu, and selecting either import data or export data.
Once selected by either of these methods, the interface is the same:
Going to the next screen, you are prompted for a data source for your process. The data sources range from .NET, to Access (remember it?), to ODBC data sources for Oracle and SQL Server.
Take note that Microsoft has a built-in OLE DB provider client for Oracle databases. However, I have always found it easier to use the provider supplied by Oracle. It would be necessary to install the Oracle client software on the Windows host to make this option available. If using Oracle as a source or target for an import or export process, I tend to go through the Windows control panel and set up my connections before using the SQL Server Import and Export Wizard. I found it easier, especially being familiar with Oracle networking.
The target options for the SQL Server Import and Export Wizard are similar to the source options.From here you get the idea that you can perform the import and export in one process, provided you get the target specifics taken care of first.
I recommend using this tool for your smaller data migration needs, even if you are not using SQL Server. The drawback is that you will need access to SQL Server software to install this utility.