Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Insert into 2 tables with foreign key.

Author  Topic 

pecunn
Starting Member

20 Posts

Posted - 2011-05-15 : 21:09:17
Hi SQL Guru,

I have 2 tables:
Customer
- customerID
- fname
- lname

address
- addressID
- customerID (reference to customer table)
- streetName
- suburb

Currently i can import all customer data from excel using data conversion and derived coloumn, My question is how i import address table which have customerID reference?

I was planing to do add fname and lname on address.csv file and assign customerID to variable with value SELECT CustomerID from Customer where fname = 'value from csv' and lname = 'value from csv' which im not sure how get 'value from csv'

Please guide me how to do this or if there is better please let me know.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-05-16 : 05:26:08
Make sure you write all new customerID's to the customer table first. Then when you load the address data add a lookup transformation to the data flow where you look up the customer ID based on the lastname and the firstname. This seems like a decent tutorial ->

http://www.youtube.com/watch?v=FB5OPkGsDtA

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

pecunn
Starting Member

20 Posts

Posted - 2011-05-18 : 03:20:21
yeah thanks it worked
Go to Top of Page
   

- Advertisement -