DB Best blog

SQL*Loader is a command line utility which performs fast loading from one or more files into Oracle tables. The utility processes the source files according to the settings in a special CONTROL file. As a result of SQL*Loader execution, additional files can be created: these include BAD file, where discarded rows are saved and ERROR file, where the utility places the rows that failed to transfer to the database.

After the database has been migrated to SQL Server 2005 or 2008 instance, you can no more use the SQL*Loader and you should look for possible replacements. A general solution for this is provided by SQL Server Integration Services (SSIS), which allow creating packages with proper source and destination settings together with custom filtering, column mapping and data transformations.

But in many cases the solution might be quite simpler - you can chose SQL Server bcp utility to perform the loading. There are particular cases when SQL*Loader can be easily emulated by bcp and in this article I will show how to do that.

First, we should check the structure of the source file. If the following assertions are true, your case is a good candidate for the bcp solution:

- The data are presented in rows and columns;

- The fields are separated by one-character delimiter; otherwise all fields have a fixed length;

- The data have character format including Unicode;

- Each field in the data file is compatible with the corresponding column in the target table;

- A single table is transferred by each SQL*Loader run.

The bcp format file is necessary if the columns in the file do not exactly match the columns of the target table, or if the column order is different. In other cases, the format file is redundant.

Now I will demonstrate an example of this approach. Suppose we have the following SQL*Loader command (I am using Windows file paths here):

sqlldr login/password@oraserver control = D:\FORMATS\myformat.ctl

and the contents of myformat.ctl are

LOAD DATA
INFILE ‘D:\mysource.txt’
REPLACE
INTO TABLE myschema.mytable
FIELDS TERMINATED BY ‘,’
<list of fields>

then the equivalent bcp command will look like this:

bcp mydatabase.myschema.mytable in D:\mysource.txt -S <sqlserver> -U <login> -P <password> -t “,” -c

Here -t option specifies the field terminator character. By default the field terminator is TAB, so we should explicitly set it to comma. Both in Oracle and SQL Server the default row terminator is newline (”\n”) and in this case we are using this default value.

The –c option instructs bcp to assign the character data type to all input lines, which makes it possible to go without explicit column definitions. Note that most of the SQL Server column types are easily converted to character and the vice versa, so this approach allows loading not only CHAR or VARCHAR data, but also numeric or datetime values stored in character format.

Written by Yuri Rusakov

Tagged as bcp, Oracle, SQL Loader, SQL Server + Categorized as Newsletters, Newsletter #4, January 6, 2009