push recordset from excel to SQL server

[Dave]

Hello,

I've searched but cannot find. There are many posts discussing
populating a spreadsheet from a recordset. I want to do the reverse.

1) select XL data into a recordset (call it "rsxl")
2) insert into an SQL server table the data from "rsxl"

Any help here is appreciated!

Thanks

 

[Jim Thomlinson]

Since I have not written data to SQL Server I will have to speak in
generalities here. Take a look at ADODB Recordsets. Create your recordset.
Connect to your database table and then update the table with the recordset.
Here is a link to get you started...

http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Tim Zych]

You can loop through the Excel table, interface with a parameterized stored
procedure using ADO to send inserts.

If this is a one-off, you can loop through the XL table and write Insert
statements to a worksheet, since SQL Server supports multiple SQL
statements, e.g.
Insert into Table (field1, field2) values (1,2)
Insert into Table (field1, field2) values (1,3)
Insert into Table (field1, field2) values (1,3)
and just paste all that into the query window and execute it all at once.

You could create dynamic SQL and use ADO, but that's probably not suitable
for a production environment. See "The curse and blessings of dynamic SQL"
at http://www.revealnet.com/newsletter-v3/0402_F.htm

You can use the Bulk Copy Process or SQL Server Integration Services (the
replacement for DTS) and import the data into a table.
But SSIS requires so much configuration I'd much rather use a flexible
stored procedure and go row-by-row unless you are talking about
tens-of-thousands of rows frequently. If you have a fast connection (e.g.
you're connected directly to the network and not connecting through a VPN),
the inserts will be very very fast.

Just some ideas.