Rough notes on SQL Server bulk insert

(I recently found these notes that I had written up in January and Feburary of 2010. I am posting them in the hope that they may be a help to me and others, even in their rough form.)

It worked to just generate all the fields, with empty fields for ones I didn’t care to fill in.

for %d in (Foo Bar Baz Quux) do bcp MyDatabase.dbo.%d format nul -T -c -f %d.fmt

This resulted in the creation of Foo.fmt, Bar.fmt, Baz.fmt, and Quux.fmt files.

Foo.fmt:

9.0
10
1       SQLCHAR       0       22      "\t"   1     FooID                            SQL_Latin1_General_CP1_CI_AS
2       SQLINT        0       4       "\t"   2     FooTypeID                        ""
3       SQLINT        1       4       "\t"   3     TitleID                            ""
4       SQLNCHAR      2       100     "\t"   4     LastName                           SQL_Latin1_General_CP1_CI_AS
5       SQLNCHAR      2       100     "\t"   5     FirstName                          SQL_Latin1_General_CP1_CI_AS
6       SQLNCHAR      2       100     "\t"   6     MiddleName                         SQL_Latin1_General_CP1_CI_AS
7       SQLNCHAR      2       100     "\t"   7     Department                         SQL_Latin1_General_CP1_CI_AS
8       SQLNCHAR      2       200     "\t"   8     Company                            SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       2       22      "\t"   9     OrgID                              SQL_Latin1_General_CP1_CI_AS
10      SQLTINYINT    0       1       "\r\n"   10    Active                             ""


If I had data for each of these columns in my data file in the natural order in which the columns were defined in the database table, I would not have needed a format file in the first place – I could just do a BULK INSERT.  But the data file only provided data for five of these columns.  So first I removed the lines defining columns that did not appear in the data file:

9.0
10
1       SQLCHAR       0       22      ""   1     FooID                            SQL_Latin1_General_CP1_CI_AS
2       SQLINT        0       4       ""   2     FooTypeID                        ""
4       SQLNCHAR      2       100     ""   4     LastName                           SQL_Latin1_General_CP1_CI_AS
5       SQLNCHAR      2       100     ""   5     FirstName                          SQL_Latin1_General_CP1_CI_AS
10      SQLTINYINT    0       1       ""   10    Active                             ""

Then I renumbered columns 1 and 6 (and updated the total columns number on line 2):

9.0
5
1       SQLCHAR       0       22      ""   1     FooID                            SQL_Latin1_General_CP1_CI_AS
2       SQLINT        0       4       ""   2     FooTypeID                        ""
3       SQLNCHAR      2       100     ""   3     LastName                           SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     FirstName                          SQL_Latin1_General_CP1_CI_AS
5       SQLTINYINT    0       1       ""   5     Active                             ""

If the fields in the data file were in a different order than the table’s natural ordering of its fields, the ordering in column 6 would have needed to reflect that.  The first column always appears in ascending order.

Obstacles overcome

During the course of getting this working, I learned the following things:

bcp -c

I needed to use bcp -c to import from a text file.  From another reference I had found, I had been using bcp -n, but that then expected a binary file and resulted in errors like this when I tried the BULK IMPORT:

(0 row(s) affected)
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Calling all columns

At first I had my data-creatin’ Python script only create data for columns I cared about.  But bulk insert (as far as I can tell anyway) expects all columns to be represented in the data file – though you don’t have to import all of them, they must be there.

References

Using a Format File to Map Table Columns to Data-File Fields

Creating a Format File

On to Bulk Insert issues on bytes.com — I was using bcp -n and getting errors like this:

The paragraph that helped me was:

First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR for a Unicode file. Other data types are only used with binary formats.

Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s