BULK INSERT Performance with format files

Hi,
We have a scenario where we want to bulk insert data into only a select
number of columns in a table.
We are currently generating a BCP file with
- data for all columns, NULL in case of columns which are not needed (These
columns are nullable in the table)
- column data is ordered as it is in the table.
We use the BULK INSERT statement.
Now, we want to use a format file and write data (into the bcp file) for
only those columns which we need. This will definitely give us a faster bcp
file creation process.
However we want to know whether using a format file will slow down the "bulk
insert" itself. Also, if there are any other issues with format files.
Thanks,
NitinAlternatively try creating a view on your
table that selects only the columns you want to insert into.
You should then be able to bulk insert directly into this view
without a format file.|||Alternatively try creating a view on your
table that selects only the columns you want to insert into.
You should then be able to bulk insert directly into this view
without a format file.|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:ugKwd8UAGHA.264@.tk2msftngp13.phx.gbl...
> Hi,
> We have a scenario where we want to bulk insert data into only a select
> number of columns in a table.
> We are currently generating a BCP file with
> - data for all columns, NULL in case of columns which are not needed
> (These columns are nullable in the table)
> - column data is ordered as it is in the table.
> We use the BULK INSERT statement.
> Now, we want to use a format file and write data (into the bcp file) for
> only those columns which we need. This will definitely give us a faster
> bcp file creation process.
> However we want to know whether using a format file will slow down the
> "bulk insert" itself. Also, if there are any other issues with format
> files.
> Thanks,
> Nitin
>
We use this successfully where I work all of the time. I have a large flat
file (about 4 GB) that has each row with a length of about 2000 bytes. We
only need about 500 of the bytes from various positions within that file.
We use a format file and can pull in the 3 million records in about 15
minutes on our test system.
One note.. Setting a ROWS_PER_BATCH size to about 1000 seemed to speed up
the process a lot for our usage.
Rick Sawtell
MCT, MCSD, MCDBAsql