Import Excel Data into Multiple Tables

Question

Import Excel Data into Multiple Tables

Autumn1008

Old Hand

Points: 336
More actions
July 26, 2005 at 12:55 pm

#67027

Hi
I have a SQL Server database which has many tables.
At the moment, all of the data for each table is held in one single excel file, one single excel sheet. Each column in the excel file represents a field, each row represents an record.
My question is this, how can i import all of the data from the excel file, into multiple tables in one attempt?
Many thanks
Autumn1008

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

If the field structure is the same for each table, then you could try this.

In the datapump source, you could try using an SQL query instead of just selecting the sheet name. It'd be something like

SELECT F1, F2, F3, etc... FROM [Sheet1$] WHERE F1 = 'blah...'

Then you would have multiple datapumps from the same source inserting into each destination table with different criteria.

--------------------
Colt 45 - the original point and click interface

Crischell Olegario Valued Member Points: 74 More actions · Answer 2

you could start by creating a stag table..

export your excel sheet into a new table in your database..

then from there you could easily do some query stuff...

then use the dts package.. you could simply use the execute sql query and transform data task..

btw, could you give examples of the files you want to import..

tnx

Glad to Help,

Crischell Olegario

Autumn1008 Old Hand Points: 336 More actions · Answer 3

thanks Crischell. Actually I am trying the similar approach to what you said. I export the excel file into a temp table which has all the same fields as the spreadsheet and distribute the data from the temp table into the destination tables. I want to create a stored procedure to do the distribution but had a hard time reading the data row by row. May be you can help me with the syntax.

Let me explain the nature of the business a bit. We need to record each project and the sampling events for each project and for each sampling event there are more than one sample collected. There are three tables "Projects" (with ProjectID as Identity column), "SamplingEvents" (with SamplingEventID as Identity column and Project ID as PK) and "Samples" (with SampleID as Identiy column and SamplingEventID as PK). Now my question is how to get the record row by row for distribution.

Thanks a lot for the help.

Autumn1008