Updating a SQL Table using Excel File

[gsathya]

Hi,

I have a requirement to update a SQL Table using an excel file. Columns in the file and the table are same. Is there is any automation to update the table without reading each record from the Excel file.

Help me in this.

Thanks

Sathya

 

[smbure]

This depends on the version of SQL Server you are running, and your permissable options. If set up correctly, MS SQL Server 7.0 has a DTC wizard that will import and append data to tables from a multitude of sources, but beware, one wrong option clicked and you could overwrite all data. The more likely choice is a module in VBA within the sheet itself to stuff all of the data in an ADODB/DAO recordset and update the table. This may still require stepping through each row, but only to get the data in the recordset and issue an .UpdateBatch command.

Hope this Helps!

smbure