Importing Excel Data Into SQL Server 2005 With A Twist!

Hi,

I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.

However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.

So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.

TIA,

Graham.