Re: VBA - Create a recordset with no records for inserting

INTP56 wrote:
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 be returned

set cn=new adodb.connection
cn.open "<your connection string to sql server>"

set rs=new adodb.recordset
rs.cursorlocation = adUseClient
rs.Open sSQL,cn,adOpenStatic,adLockBatchOptimistic,adCmdText

'disconnect the recordset:
set rs.Activeconnection=Nothing
cn.close

' add your records to the recordset as normal
for <each record in the Excel dataset>
rs.AddNew
....
rs.Update
next

'reconnect the recordset
cn.Open
Set rs.ActiveConnection = cn

'Call the batch update method:
rs.UpdateBatch

rs.close: set rs=nothing
cn.close: set cn=nothing


You should probably investigate using SQL Server Integration Services
(SSIS) rather than ADO for this task. Using SSIS, data can be read
directly from the Excel spread*** and imported into the SQL Server
table ... much quicker than with ADO.

Bob Barrows



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.