multiple tables

Hi

I am trying to import an excel spreadsheet into a sql server database. The problem is I need the information in the spreadsheet to be imported into 3 different tables. I thought of transfering all details into a temporary table and then setting 3 triggers for each transfer of information. But this seems very inefficient. I know there should be an easier way to do this in dts.

Any help would be greatly appreciated

ThanksCreate a DTS package with Excel spreadSheet as the Source and the SQL Server as the Destination.
Create 3 Destination SQL Server and Define a connection between the one Excel Source and the three SQL Server destination table. It should just work perfect for you.|||Hi

Thanks for your reply...is that a connection using the transform data task??

Thanks|||Yes you have to set the Transform data task.|||Hi

Is this just using a for loop in the activeX script and incrementing each time...I have been trying to do this, but it keeps returning an error.

Thanks|||In the DTS tool bar there is a tool called Transofm task. Just select the source(excel) and Destination(sql server) and run it. You don't need to code any ActiveX script unless you plan to do some data messaging during the import/|||Also refer to http://www.sqldts.com for code examples on ActiveX & DTS goodies.

Multiple Rows

Hi, can anyone think of how to get round this. I am using MS Excel Query on my Oracle database.

I want to make a spreadsheet showing account number, customer 1, customer 2, customer 3. There is an accounts table, an accounts_links table which links the customers to the accounts and a customers table.

On the accounts_links table there is one row for each customer on an account with a "holder number" to say if it is customer 1, 2 or 3.

At the moment I am only bringing back accounts where there are 3 customers. Some accounts will only have 1 or 2 customers.

I thought to bring in the accounts_links and customers tables in three times now it is just multiplying all the rows together!

Can anyone think of a way round this to run it in one sql, I don't want to have to use excel functions.

Thanks,
BethIf I've interpreted your issue correctly, here is one suggestion to try:
SELECT a.account_number, MAX(CASE a1.holder_number WHEN 1 THEN c.customer_name END) customer_1,
MAX(CASE a1.holder_number WHEN 2 THEN c.customer_name END) customer_2,
MAX(CASE a1.holder_number WHEN 3 THEN c.customer_name END) customer_3
FROM accounts a, accounts_link al, customer c
WHERE a.a_primary_key = al.al_primary_key
AND a1.al_primary_key = c.c_primary_key
GROUP BY a.account_number

You'll have to edit this to provide the appropriate columns for the WHERE join columns.

Originally posted by elisabeth
Hi, can anyone think of how to get round this. I am using MS Excel Query on my Oracle database.

I want to make a spreadsheet showing account number, customer 1, customer 2, customer 3. There is an accounts table, an accounts_links table which links the customers to the accounts and a customers table.

On the accounts_links table there is one row for each customer on an account with a "holder number" to say if it is customer 1, 2 or 3.

At the moment I am only bringing back accounts where there are 3 customers. Some accounts will only have 1 or 2 customers.

I thought to bring in the accounts_links and customers tables in three times now it is just multiplying all the rows together!

Can anyone think of a way round this to run it in one sql, I don't want to have to use excel functions.

Thanks,
Beth