Re: VBA - Create a recordset with no records for inserting
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Fri, 19 Oct 2021 10:34:03 -0400
I apologize in advance if I'm being dense.
First, Excel 2003, ADO 2.8, SQL Server 2005.
What I'm trying to do is go through an Excel workbook, gather data,
and insert it into a table in SQL Server.
In an Excel VBA module?
To start, I made a stored procedure in SQL Server that inserts one
record at a time. Just calling that procedure repeatedly took 50
seconds to insert 100,000 rows. And I did use transaction bounding to
commit at the end and in groups of 10,000. No noticable difference,
or whether the table was empty or already had 500,000 rows in it.
I have been able to create a disconnected ADO record set, add 100,000
rows, and save it to my local disk. That takes about 6 seconds to
write the same dataset.
What I would like to do now is insert that recordset into the
database. What I can't seem to figure out, is how to get that dataset
into the table without retrieving what's already in the table first.
Could someone show me a working example of how I could create a
record set to attach to an ADODB.Command , load the recordset
locally, and then execute an ADODB.RecordSet.UpdateBatch to move the
data into the database in one shot, without having to retrieve what's
in the table first?
dim rs as adodb.recordset
dim cn as adodb.connection
dim sSQL as string
sql = "select ColumnA,ColumnB,Columnc,Columnd,Columne," & _
"Columnf, Columng from yourtable where 1=2"
' the "where 1=2" guarantees that an empty recordset will b