Bulk Insert

[gromit114]

Am currently using a DTS package to import various csv files into a MSSQL7 database. However one of these files has 3,900,000 rows and takes 18minutes to run i really would like to speed this process up and have been looking into BULK INSERT. however because the table contains different data types such as INT, Varchar, datetime it just falls over. The data is '|' seperated. Does anyone have any ideas on how to get over this!

 

[AshleyRickards]

When I import large files I first copy them locally to the server so I'm not going through the T1 or across domains. I've imported 6.4 mil in less than 3 min. I might work. One other thing .. are there existing index's? This might be slowing it down. If so drop and recreate the index after import.

HTH Ashley L Rickards
SQL DBA

 

[gromit114]

To Ashley,
Thanks for the response however the file is stored locally on the server. This is done as a batch file seperatley before the dts process. Their is also no indexes on this table as i only use it as a holding area before i query it to populate the final table. So this is why i was looking at bulk copying!

 

[tlbroadbent]

DTS will do Bulk Copy operations! I've found that DTS is quite fast if the proper options are set.

If the DTS does a Transform Data Task make sure that the Use Fast Load option is checked. You will also get some performance gain if you check Table Lock and uncheck Check Constraints. These options can be found on the Transform Data Task Properties Option tab.

You can also use DTS to perform a Bulk Insert Task. Bulk Insert task is very quick because there is no data vaildation as you can have in the Data Transformation Task.

Whether you use DTS, BCP or T-SQL Bulk Insert make sure you review the topic "Optimizing Bulk Copy Performance" in SQL BOL. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[gromit114]

Thanks for the suggestions but this is what i currently do at present and is not really an answer to my question i need to know how to get over having various data type in a table and then trying to bulk copy a text file in to that table!!

Regards

Leigh

 

[tlbroadbent]

Are you using T-SQL Bulk Insert or a DTS Bulk Insert Task?

Bulk Insert handles data type conversions as long as the data in the text file is convertible to the data type in the data base. No special coding is required. Could you provide your code, some sample data and the exact message you get when you run it? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.