BULK INSERT in SQL Server 7 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BULK INSERT in SQL Server 7

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-27 : 11:21:55

Chetan writes "Hi SQL Team,

Requirement: To bulk copy the data file (~20MB) in size to a table in the sql server 7

hardware:
Pentium 2 with
Windows 95/98/NT/2000 sp 2
RAM: 256MB

s/w: MS SQL 7 sp 2

Problem: I am using the BULK INSERT command supported by the SQL Server 7
which is pretty slow and is taking approximately 5mins to copy the data. I
want to achieve the same in less than a minute how can i do that??

As per the manual, they say BULK INSERT is faster compared to the
bcp cmd of sybase.

Currently i am using the following bulk insert command to copy:

BULK INSERT targetTable FROM bcp1.txt WITH (FIELDTERMINATOR = ',' ,
ROWS_PER_BATCH = 300000 , DATAFILETYPE = 'char' , ROWTERMINATOR = '\r\n' ,
TABLOCK ) ";

This table has an index on the primary.

Suggest me what all parameters I have to set for this command to get an
optimal performance or any other way to make a faster copy.

Waiting for your response

Regards,
Chetan"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:28:20

If your data file is ordered by the primary key you can use the ORDER clause of BULK INSERT to improve performance.

setBasedIsTheTruepath
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-27 : 11:54:04

You can also DROP the index(es) before you do the BULK INSERT and re-create them afterward.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-27 : 12:25:20

To get the fastest bulk insert make sure there are no indexes or triggers on the table and that the database allows non-logged operations.
Take out the batch size parameter - the insert won't log row inserts so there is no problem with the transaction log.
Make sure the database is big enough to hold the data so that it doesn't keep having to increase in size (which will probably fail anyway with bcp).

I would advise against doing this into a production table. Insert into an empty table in it's own database then copy from there to production - but it sounds like you haven't time for that.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources