I have many files in on a network drive. I want to write most efficient code to import all the files into one table.
Note: I need to create the table based on what is in the files. For example if the excel file has three columns called col1, col2, col3 and col55 I need to create a table [TABLE1] in sql and put one set of files into it. Then I have some SPs I would run. After that I will move SELECTED data into another table and delete the [TABLE1] table.
This step will repeat again and again as new batch of excell files may have col2, col5, col1, col44, col3 etc. But here is good part. the selective data I need from all the forms will be same. for example from above I will be doing
INSERT INTO [TABLE 22] SELECT col1, col3 FROM [TABLE1]
I do not want any links as they disappear for the next user. That why I am giving 500 points for it. I want full example code. I am open to any suggestions.
I am using SQL 2000 and VB6