I am working on a project where data from a large number of cells (5,000 +/-) from an Excel spreadsheet are read and stored in several tables. There is a table in the DB that has a row for each Excel cell that is read. This table (let's call it ExceltoSQLMap) basically maps the Excel Worksheet and Cell to the DB Table and Field where the Excel data is stored.
I was able to write a fairly simple stored procedure to return the value from any given table/column. In the stored procedure, I build a string from input parameters that looks something like: 'Select ' + @FieldName + ' FROM ' + @TableName.
I am calling the Stored Procedure from VB code. It works fine, but it takes something like 3 minutes to iterate over all 5,000 fields. There must be a way to generate the dataset from within a stored procedure, but I can't conceptualize how to do this.
The table ExceltoSQLMap from above has columns for TableName and ColumnName. So I started with a select statement that looked like: SELECT TableName,FieldName FROM ExceltoSQLMap. Now what do I do to join the actual tables where the data is stored?
Any help or thoughts would be appreciated