[RESOLVED] items in Dictionary(Of TKey, TValue) or List, To Sql Server table in one call?

[jcis]

I'm using VS 2005, i'll be moving info from a Dictionay to Sql Server table, i can do it row by row (1 insert command by item), but i have almost 1000 files loaded with 300.000+ records and i'm implementing different way to make this faster (table indexes, intermediate tables, etc..). One thing that would make all this faster would be a direct copy from dict/list/array to sql server table, is it possible? i could join/concat all these 1000 records (3 fields each) and send to db something like:

Insert...
Select ...
Union all
Select
Union All
Select..

but as you can see this this doesn't look very nice, i doubt there is another way but just in case, if anybody knows, it would be nice.

The reason i need C# between the files and the table is because much data processing is done in C#, data is not inserted into db just as it comes from files.

Good old DTS packages could do the job but i would have to replace this C# App by the DTS pack, and also im using Sql Server 2008 (DTS's have been deprecated, i don't understand why)

[techgnome]

Why isn't it in a Datatable? is there a reason it's in a List/Dictionary?

-tg

[jcis]

I can use anything, my last idea was adding this post-processed data in a Dictionary using chunks of 1000 rows then trying to do something like a Bulk insert then reading another 1000 and so on, i can use any container, but is there any way to do something like a bulk insert so i don't have to insert 1 record each time? I've been reading some examples about SqlBulkInsert but all these examples take data from a CSV file, maybe there is a way to do the same using some container in memory, like array/list/dictionary or a datatable.

I've come to a working version of whta i wanted, but it's ugly:

- I add 100 1000 records( taken from file and post processed) to a Dictionary ensuring they are all unique records (this is part of this post-processing)
- I use a StringBuilder to concatenate all queries like:

Insert into temp_table
select field1, field2, field3
union all
select field1, field2, field3
union all .. and so on 1000 times.

- After that in a stored procedure i join these temp table with the real table to the the records not present in the real table and just insert those records
- Finally i erase the temp table, erase the stringbuilder, and proceed to the next loop.

It's working fast now, but it's ugly, it's inserting a giant sql string, and also i dont like to create Sql Strings by concatenating them with values, it's not a good practice, i usually use SqlParameters when i can, but i can't use them when doing this ( i would need like 3K params i think)

So, if something like a Bulk-Insert can be made directly from data in memory, it would be the best.

[szlamany]

You really only have two choices.

Run each insert one at a time - doesn't matter if it's a datatable or whatever - it's still taking t-sql dml statements in some fashion. There isn't some magic way to make them bulk-transmit...

or...

Run bulk insert.

300,000 rows is a point where bulk insert is the heavy favorite. If you had a million rows you would only ever consider bulk insert - right?

Bulk insert is different and better for large jobs because it runs on the server with said server opening a text file for the import of data. Usually bypassing logging in some way to actually make the speed-enhancement even better.

[techgnome]

I would read the data into a datatable, mainuplate it as needed then feed that (as a datatable) to the =http://social.msdn.microsoft.com/Search/en-US?query=sqlbilkcopy&ac=3]SQLBulkCopy[/url] class ... set the DT as the source, give the table name as the desitination, set the mappings, open the connection, and suck away.

-tg

[szlamany]

Read a bit of this

http://msdn.microsoft.com/en-us/libr...ql.100%29.aspx

75% of the way through the read you get to Optimizing Bulk Load...

You have to remember that bulk load runs on the server - whether from a text file (old school) or some memory stream - it still runs locally and you have other issues / constraints (such as keeping up) that matter.

Are you going to have 300,000+ rows daily? Are you trying to automate such a process?

[jcis]

Thanks i'll follow those links.

This only runs on demand, the info contained in these files is the typical report provided by IIS about different websites, this is sort of report is typically used by webmaster tools like cPanel to show all info/statistics from websites, the only info i take from it is IP address/cell phone number/carrier, i use substring to get this info from each line but it's not at fixed positions so i first i search for known text at left/right of each value, this is the post-processing i was talking about.