Idea needed - bulk insert into database

Not sure how to do this:

I call a vendor's web services and bring back rows of data. The data is in array and i can loop thru it. For example, 1000+ rows.
I need to insert these rows into a database table. I dont want to loop thru and keep
inserting into the database one row at a time. How can I do this without keep calling the database to insert?

Should I dump the rows into a text file or Excel and import the excel?

 foreach (var item in retVal.assetArray)
          //dont want row by row insert. How to insert all at once in DB?

Open in new window

Who is Participating?
Mark WillsTopic AdvisorCommented:
If it is well formatted data, then dumping to either excel, or, flat file (properly formatted) will work.

Makes it a bit more involved if you have to do a lot of work to format first though, or, if there is a variable format of the import file.

I would be inclined to create a stored procedure in SQL Server to do the import - maybe to a staging table first (to validate formats etc) via Bulk Insert and then an insert query into the destination table.

So, your code retrieves the data (and hopefully doesnt have to format) and outputs to a fully formatted CSV (or similar) into a disk location accessible via SQL server and then calls the stored procedure.

Does that make sense ?
CamilliaAuthor Commented:
So, do an import into excel or flat file, call a stored proc to dump into a staging table (to double check the format and stuff) and then a stored proc to dump into the prod table?
Anthony PerkinsCommented:
Here are your options as I see it (in order of preference):
1. (SQL Server 2008 only) Tabled valued parameters.
2. Xml (using OPENXML() or Xml Data Type methods
3. Delimited string.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

CamilliaAuthor Commented:
My manager says we can open one connection, loop thru and insert. He says this is sql server and can handle it. We might have 10,000+ rows. I dont think it's efficient , even if we only open one connection.  Our issue is looping thru to get each row and then insert....

What do u mean by #3? a textfile and bulk import?
Anthony PerkinsCommented:
No. You would build a delimited file in memory and pass it in a single parameter.  In SQL Server you would then have to parse it to retrieve the relevant parts.  This is by far the most complicated and least efficient method.
CamilliaAuthor Commented:
it's SQl 2005. Opening one connection and inserting (as my manager says it), is ok to do?
I dont want #3. My other option is what i have now: insert to textfile or excel and do bulk import.
Mark WillsTopic AdvisorCommented:
At a few thousand lines, it is not too much of a problem...

At tens of thousands it becomes remarkably more significant.

Single inserts also place load on transaction logs etc, and as a "BULK" insert then you can retain a certain level of transactional integrity (ie the file loads of not).

So, if the bulk (pardon the pun) of the work to be imported is less than a few thousand rows, then sure, single inserts is an easy option to take - it does give the opportunity to do any ETL / validation as you go so you know that committed data is "clean".
CamilliaAuthor Commented:
Bulk insert does or doesnt NOT put a load on transcation log?

I think we will have 10,000+ rows...we probably can break it into chunks...

still not clear on how to go about this....
Mark WillsTopic AdvisorCommented:
It doesnt have to - especially if using a staging table.
If loading directly into an already populated table with indexes then there are some additional considerations ( like transaction logging and table locking etc )

Have a read of the bulk insert :

And also have a read of :

Just because you are doing "bulk" it doesnt always mean that it is "lighter" on the systems - sometimes it can mean full transaction logging etc... And breaking into chunks means that subsequent chunks are going to an already populated table - so - please read the above it can have consequences.

And that is also why I said above that a few thousand rows probably doesnt make much difference (except maybe the processing time in between each row). And thats really where SQL can do a heck of a lot for you in terms of reading and unpacking a well formatted string (be it excel, csv, xml etc). It can under well defined conditions relieve other loads on the system too. But you need to be sure that your recipient tables are such that they dont automatically invoke full transaction logging (and locking and other resource waits) for it to be significantly advantagous over other very well developed manual methods.

Do you want an example to do a bulk import ?
CamilliaAuthor Commented:
Thanks, let me read the links. I'll google for bulk import examples.
store the result in text file using "string builder"

it will reduce database transaction and than run below command.

 FROM 'C:\classes.txt'

for more information please refer below link...
Mark WillsTopic AdvisorCommented:
>> it will reduce database transaction

Well, that is not necessarily true. Just because you use a "BULK" type import doesnt automatically mean that it is being minimally logged. It does depend a lot on recovery model and the recipient table structures in place. Generally, if a non-empty table has indexes, bulk imports are fully logged.

One of the links above points to an interesting topic on optimising bulk import performance :

As you will read, there are quite a few different factors...

Generally, that is also why I use a staging table to bulk import into. It has no real constraints, it is empty, no indexes etc so it is as quick as it can get.

True, you then need to post from the staging table to live tables and they are likely to be fully logged, but you do have options to first validate data, verify constraints and ranges, produce various reports (and possible recovery points), even control the volume of activity in accordance with time and activity to minimise live performance impacts for other users.

Dont get me wrong, I am a supporter and advocate for bulk operations, just need to make sure they are being done the right way otherwise you wont necessarily get the huge benefits over other methods (again depends a bit on size and frequency of import).

Just as an aside, the link above says that BCP will not deliver the same performance, and while generally that is true, it does depend on systems and sometimes having a third party tool / external program like BCP as a parallel load can achieve a positive difference - it allows you to control triggers, constraint checking, some data validation, batch sizes, sort order, keeping identity values etc. So, it can be a valuable tool for an "involved" bulk import.

For rowset type operations there are a few choices and possibly best summed up in :

In terms of a "clean" and "well formatted" file for import make sure of the following :
1) Column Headings
2) quote encapsulated string fields
3) ideally a unique field seperator
4) ideally carriage return and line feed (or DOS / Ascii format is best)
5) date formats are clearly defined and identifiable - ideally yyyymmdd or yyyy-mm-dd
6) price columns or dollar values are shown as decimals and not formatted with dollar signs
7) regional settings that can give rise to formatting (like decimal points, dates etc) are accounted for
8) plain data - ideally no footers or sub headings or variable format rows.
9) if needed, a format file can be used to help understand the format of the import file (see the ms link from keyu)
Ryan McCauleyData and Analytics ManagerCommented:
Your code looks like C#, though you don't explicitly specify. If you can get a DataTable objects with the right schema on it, you can use the SqlBulkCopy class to cram massive amounts of data into your database at once, like in this example:

It's massively faster than a row-by-row insert, especially for very large row counts. It does require that your DataTable have a matching schema and I believe you need to include a value for every column in the table (even if the value is blank or null), though it should get the job done without using an external tool or loading the file directly into SQL with the BULK INSERT command (not that it's bad, just requires access to disk).
CamilliaAuthor Commented:
let me look at your link, ryan.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.