Anyone know how to Bulk Insert to SQL from string Array with comma delimited columns in Array

Question

I have a string array in C3 that gets populate with Yahoo Historical data that has data in the array like this..

string url = string.Format("http://ichart.yahoo.com/table.csv?s={0}&a=2&b=1&c=2010&d={1}&e={2}&f={3}&g=&ignore=.csv",
    SymbolSelect, CurrentMonth, Today, CurrentYear); 
WebClient web = new WebClient();
string data = web.DownloadString(url);
string[] rows = data.Split("\n".ToCharArray());

Array rows--->> Date,Open,Low,close,Volume,AdjClose

The array rows will have each row as stated above with 6 columns of data per row. I have no idea how to insert that into a sql table? I know how to insert to a sql table, but not like this from this array structure. Anyone know how to insert every single row of thousands with this structure??

Thanks in Advance!

gpmcadam · Accepted Answer · 2010-03-07 04:29:56Z

1

Depending on your SQL server technology, this may apply:

A SQL feature (since SQL-92) is the use of row value constructors to insert multiple rows at a time in a single SQL statement:

INSERT INTO ''TABLE'' (''column-a'', [''column-b, ... '']) 
VALUES (''value-1a'', [''value-1b, ...'']), 
       (''value-2a'', [''value-2b, ...'']), 
       ...

This feature is supported by DB2, SQL Server (since version 10.0 - ie. 2008), PostgreSQL (since version 8.2), MySQL, and H2.

Insert (SQL) - Wikipedia

answered Mar 7, 2010 at 4:29

gpmcadam

6,5902 gold badges36 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Firestrand · Accepted Answer · 2010-03-07 04:34:47Z

1

The most efficient way is to use SqlBulkCopy.

The only problem with that approach is that you will have to project your data into a DataTable, DataRow[], etc. in order to use SqlBulkCopy

answered Mar 7, 2010 at 4:34

Firestrand

1,3131 gold badge10 silver badges19 bronze badges

2 Comments

Josh Over a year ago

You can actually implement IDataReader too which is a lot more efficient in many cases because you don't need to load up a table with the data if you already have it in memory in another form.

Firestrand Over a year ago

@Josh Einstein, good point about creating an IDataReader that can interpret the incoming comma separated list.

Marc Gravell · Accepted Answer · 2010-03-07 08:22:54Z

I'm reading "bulk insert" as SqlBulkCopy. There are two ways to work with SqlBulkCopy. The first is to pre-process your data into a DataTable. This is simple and easy, but is only suitable for small-to-moderate data due to the requirement to buffer all the data into a DataTable first!

The second is to create an IDataReader; this is a streaming API, so can be used for huge forwards-only data sources (such as massive log files). I wrote a SimpleDataReader that you can see here (ignore the xml; focus on the usage) that I use in a few places. It works fine; all you do is:

in the constructor, tell the base constructor the types for the columns
in DoRead attempt to read the next row (perhaps ReadLine on a text-reader), and either call SetValues and return true, or return false if there is no more data
override DoClose if you want to clean up anything once the data is consumed (close the file, perhaps)

Collectives™ on Stack Overflow

Anyone know how to Bulk Insert to SQL from string Array with comma delimited columns in Array

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related