CODEWITHSUNDEEP
c#
MB.
MB.

Reputation: 79

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

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!

Upvotes: 2

Views: 3765

Answers (3)

Marc Gravell
Marc Gravell

Reputation: 1062905

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)

Upvotes: 0

Firestrand
Firestrand

Reputation: 1315

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

Upvotes: 1

gpmcadam
gpmcadam

Reputation: 6560

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.

Upvotes: 1

Related Questions