August 13, 2003 at 6:36 am
Hi,
I am using ADO for interfacing with SQL server. Is there any way to insert records in bulk using ADO? I want to insert ~1000 records into the database.
Are there any other mechanisms for bulk insertion?
Thanks,
Krishnan
August 14, 2003 at 12:00 am
Recently I had a similar issue. I had to upload around 1 lac record from a text file into the sql server database. I will let you know what I DID.
I created a store procedure and accepted all the column values as parameters.
From the frontend I concatenated around 800 (e.g.) values separated by a delimiter eg. ("|") and sent them to the store procedure. The store procedure then inserted these records at one go. The performance achieved was good. Do tell me u'r views.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 14, 2003 at 12:17 am
I have not understood your solution fully. Can you please clarify me about what you mean when you say -
quote:
...I created a store procedure and accepted all the column values as parameters.From the frontend I concatenated around 800 (e.g.) values separated by a delimiter eg. ("|") and sent them to the store procedure. The store procedure then inserted these records at one go...
You say you created a SP with as many parameters as there are columns. Then you also say you concatenated the values of each column into a string and then passed it on to the server. I am a bit confused here. Can you please clarify??
Thanks,
Krishnan
August 14, 2003 at 12:58 am
Sorry for the confusion.
Suppose my text files had this data
1|Deep|5000
2|Anil|5000
....
and so on
I would create a sp like this
create proecure @c1 varchar(8000),@c2 varchar(8000),@c3 varchar(8000)
To this store procedure I would pass data as
1|2| for c1
Deep|anil| for c2
5000|5000| for c3
The store procedure would then parse the string and create insert statements.
I hope I am clear this time.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 14, 2003 at 4:50 am
A different way to do the same thing is to build it up as an XML doc, use OpenXML on the server.
ADO doesn't support bulk load, though if you can live with the overhead obviously you can disconnect a recordset, add all your data, connect and call batchupdate, it will post them over.
Other potential options are to create a delimited file, copy to the server, have a server side process grab it - DTS, Bulk Insert, whatever.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply