Data Entry in Excel to SQL Server 2005

[ease20022002]

Hi, I am developing a SQL Server 2005 reporting database. I will have a reporting template as a UI in Excel 2003 that will call stored procedures to populate the Excel reports. I've done that in the past and it is easy. I will also have a data entry template (UI) that users will use to insert some data into Excel and then press a button that would insert the Excel data into a SQL Server 2005 table.

I am wondering which method of the following methods is more efficient in terms of speed, and is more "standardized". If there is another method not mentioned below, please make a suggestion.

1) I can create an ADO link to the database table in the Excel file and have the records in Excel file be written to the table one field value at a time. I know how to do this and I would only be entering in rows that exist on the worksheet, avoiding records that were all nulls. The total amount of records would not exceed 5000 rows and 15 columns. It shouldn't take that long as I have done this before.

2) I was wondering if I could use a stored procedure that Excel would call (via ADO) in the database that would perform a "bulk insert" from the Excel file into the database table without creating any null records. I assume that would be much faster, but I am not sure if that is the standard way of exporting data from Excel to SQL Server

If someone believes #2 is the better option, could you briefly give me the conceptual code structure in both Excel and the stored procedure housed in SQL Server in order to use this method? Thanks...

Any ideas/suggestions are appreciated.

Thanks

 

[Crowley16]

have you considered using SSIS?

record an excel import package and modify it as needed...

--------------------
Procrastinate Now!

 

[ease20022002]

Not if I want the User to be able to push a button and have the records either be written value by value to the destination table. I user SSIS and the amount of time to develop and maintain and have it execute whenever the User wants to update the data would become cumbersome, as opposed to pushing a button and having some VBA call a stored procedure in the DB for a "Bulk Insert" of the worksheet, OR, opening the DB table and writing value for value into the DB table.

The user of the excel spreadsheet may make multiple updates daily. It is a UI, not a flat file. And I don't want to have the user do a save as and then have some tricky SSIS ETL package identify "action" on the file share to import the file. Way too much effort. Of the two I mentioned, I am hoping there is bulk insert that is hyper fast.

Thanks

 

[ease20022002]

I forgot to mention that I might have up to 100 files hitting the same table, but each file will hit a different page in the table b.c each file can only work with a certain indexed portion.