Most efficient way of entering data into a SQL database

[Matt]

Sorry this isn't necissarily ASP, but it is VBScript & ADO and I figure that
you all would be the best to ask.

I am using a VBScript that reads a binary file and imports all that
information into a database. The file is a proprietary financial database,
and I am reading out names, accounts, and transactions into 3 2-dimensional
arrays. Once the script is done, I want to import this data into 3 seperate
tables (names, accounts, & transactions) in my database. I could import it
using a For loop (like below) on each array and calling a separate SQL
Insert command for each row that I will be inserting into the database, but
I think that will be the most inefficient way of doing it.

For i = 0 to Ubound(array1)
oCmd.CommandText = "Insert Into..."
oCmd.Execute
Next

My other idea was a Stored Procedure that I could pass a delimited string,
parse the string in the SP and use a loop to Insert each row. I'm not sure
how efficient that would be though. Or the other way, would be to export it
all out to a csv and import it via DTS. Anyone have any ideas on what would
be the best way to mass load this data. It will be run several times a day
on several different binary files, so I really need it to be as efficient as
possible.

Thanks for any help,
Matt

[Aaron Bertrand - MVP]

How about BULK INSERT? We use this for loading massive log files, and then
we distribute the data with a query.

For some of our processes, we have an external app that parses logs and
builds CSV or TSV files that are friendlier for BULK INSERT (it discards
some columns, and uses metadata to discard some rows, instead of BULK
INSERTing extra crap for nothing).

I can't imagine it would ever be practical to have an ASP page parse a
file...

--
Aaron Bertrand
SQL Server MVP





"Matt" <MattnoChilders.spam_@msn.com> wrote in message
news:uqwvgHCxDHA.2456@TK2MSFTNGP12.phx.gbl...

> Sorry this isn't necissarily ASP, but it is VBScript & ADO and I figure

that

> you all would be the best to ask.
>
> I am using a VBScript that reads a binary file and imports all that
> information into a database. The file is a proprietary financial

database,

> and I am reading out names, accounts, and transactions into 3

2-dimensional

> arrays. Once the script is done, I want to import this data into 3

seperate

> tables (names, accounts, & transactions) in my database. I could import

it

> using a For loop (like below) on each array and calling a separate SQL
> Insert command for each row that I will be inserting into the database,

but

> I think that will be the most inefficient way of doing it.
>
> For i = 0 to Ubound(array1)
> oCmd.CommandText = "Insert Into..."
> oCmd.Execute
> Next
>
> My other idea was a Stored Procedure that I could pass a delimited string,
> parse the string in the SP and use a loop to Insert each row. I'm not

sure

> how efficient that would be though. Or the other way, would be to export

it

> all out to a csv and import it via DTS. Anyone have any ideas on what

would

> be the best way to mass load this data. It will be run several times a

day

> on several different binary files, so I really need it to be as efficient

as

> possible.
>
> Thanks for any help,
> Matt
>
>

[David Morgan]

You say the database is proprietary, but you can access it from ASP/VBS so
it can't be that bad. Does it output to CSV or Support ODBC?

I had a similar issue when I needed to write web reports over a legacy
FoxPro database... After mucking about trying to get the data imported,
using linked servers and all sorts of things I ended by accessing the data
from the FoxPro files SQL Stored Procedures and the OPENROWSET function. It
is beautiful and allows you to join tables from disparate data sources all
in to one nice result set which you can then pass back to ASP.


"Matt" <MattnoChilders.spam_@msn.com> wrote in message
news:uqwvgHCxDHA.2456@TK2MSFTNGP12.phx.gbl...

> Sorry this isn't necissarily ASP, but it is VBScript & ADO and I figure

that

> you all would be the best to ask.
>
> I am using a VBScript that reads a binary file and imports all that
> information into a database. The file is a proprietary financial

database,

> and I am reading out names, accounts, and transactions into 3

2-dimensional

> arrays. Once the script is done, I want to import this data into 3

seperate

> tables (names, accounts, & transactions) in my database. I could import

it

> using a For loop (like below) on each array and calling a separate SQL
> Insert command for each row that I will be inserting into the database,

but

> I think that will be the most inefficient way of doing it.
>
> For i = 0 to Ubound(array1)
> oCmd.CommandText = "Insert Into..."
> oCmd.Execute
> Next
>
> My other idea was a Stored Procedure that I could pass a delimited string,
> parse the string in the SP and use a loop to Insert each row. I'm not

sure

> how efficient that would be though. Or the other way, would be to export

it

> all out to a csv and import it via DTS. Anyone have any ideas on what

would

> be the best way to mass load this data. It will be run several times a

day

> on several different binary files, so I really need it to be as efficient

as

> possible.
>
> Thanks for any help,
> Matt
>
>