Bulk inserting into Sql Server

Hello Everyone,

I am currently struggling with a problem bulk inserting data into sql server.

The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).

What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.

1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.

2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).

3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).

Does anyone have any idea's on a cpu friendly approach to this problem ??

Thanks in advance..

Gary.You can actually use BCP

Bulk insert and Double Quotes

Hi,

i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

this command will import the files fine

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","')

but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

so my question is simple who do i do this with a format file ?

Code Snippet

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","'

, FORMATFILE = 'format_file_path')

|||

that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

I have found the solution my self by accident

you can use the following format file to do it

9.0
19
1 SQLCHAR 0 255 "\",\"" 1 Field1 Latin1_General_CI_AS
2 SQLCHAR 0 255 "\",\"" 2 Field2 Latin1_General_CI_AS
3 SQLCHAR 0 255 "\",\"" 3 Field3 Latin1_General_CI_AS
4 SQLCHAR 0 255 "\",\"" 4 Field4 Latin1_General_CI_AS
5 SQLCHAR 0 255 "\",\"" 5 Field5 Latin1_General_CI_AS
6 SQLCHAR 0 255 "\",\"" 6 Field6 Latin1_General_CI_AS
7 SQLCHAR 0 255 "\",\"" 7 Field7 Latin1_General_CI_AS
8 SQLCHAR 0 255 "\",\"" 8 Field8 Latin1_General_CI_AS
9 SQLCHAR 0 255 "\",\"" 9 Field9 Latin1_General_CI_AS
10 SQLCHAR 0 255 "\",\"" 10 Field10 Latin1_General_CI_AS
11 SQLCHAR 0 255 "\",\"" 11 Field11 Latin1_General_CI_AS
12 SQLCHAR 0 255 "\",\"" 12 Field12 Latin1_General_CI_AS
13 SQLCHAR 0 255 "\",\"" 13 Field13 Latin1_General_CI_AS
14 SQLCHAR 0 255 "\",\"" 14 Field14 Latin1_General_CI_AS
15 SQLCHAR 0 255 "\",\"" 15 Field15 Latin1_General_CI_AS
16 SQLCHAR 0 255 "\",\"" 16 Field16 Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 Field17 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 Field18 Latin1_General_CI_AS
19 SQLCHAR 0 255 "\r\n" 19 Field19 Latin1_General_CI_AS