SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Import from excel to more than one db table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 02/07/2008 :  05:54:05  Show Profile  Reply with Quote
Hi,

I have a large excel file that contains some contact information and includes the following columns:
Company Name, Company Fax, Company Address, Contact Person

In the db I have 3 tables that I need to insert into:
Company table
id ([uniqueidentifier] NOT NULL DEFAULT (newid())
Company Name nvarchar (200)
Company Fax nvarchar (200)
Company Address nvarchar (200)

Contact table
id ([uniqueidentifier] NOT NULL DEFAULT (newid())
Contact Name nvarchar (200)

Contact_Company table
id ([uniqueidentifier] NOT NULL DEFAULT (newid())
contact_id [uniqueidentifier] NOT NULL,
company_id [uniqueidentifier] NOT NULL,


In addition, the excel file will contain the company name more than once (for every contact person in company new row with company name).
I need to insert into table Company the company only once.
I then need to insert the Contact details into the table Contact
Finally, i need to insert into Contact_Company table both the company_id and contact_id

Problems:
-How do i insert into Company table the company only once from excel?
-How do i insert into Contact_Company the correct contact_id and company_id so that the right contact person will be assigned to the company?

Thanks for the help




Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 02/07/2008 :  13:01:15  Show Profile  Reply with Quote
If you're relying on SQL Server to create the GUID PKs via the defaults, then you'll have to split this into two separate data flow tasks to ensure that the parent tables have been populated before the intersection table.

DFT 1
1. Use a Multicast component to split the data flow: one branch will handle the Company inserts, the other will handle the contact inserts
2. Add an Aggregate component to each branch, grouping by all columns that you wish to remain in the data flow (i.e. Company Name, company Fax, and Company Address for company). This will leave only distinct combinations of these attributes in each branch
3. Add an OLEDB or Native SQL Server destination adapter to each branch and point it at the relevant table, mapping the columns as appropriate.

DFT 2
1. Configure the source adapter as per the previous data flow task, but uncheck all columns other than Company Name and Contact Person.
2. Add a Lookup Component to return the ID from the Company table based on the Company Name (+ any other attributes that make up the natural key).
3. Repeat step 2 for the Contact Table.
4. Add a destination adapter pointing at the Contact_Company table.

Mark
Go to Top of Page

Qualis
Posting Yak Master

USA
145 Posts

Posted - 02/07/2008 :  13:06:35  Show Profile  Reply with Quote
Do you really need the XRef table? Will you be associating a contact with multiple companies?
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 02/08/2008 :  18:22:25  Show Profile  Reply with Quote
Hi,

Thanks mwjdavidson I will try your suggestion.
Qualis if you mean the Contact_Company table then yes i need it as i am working on an existing project that already references the 3 tables i mentioned and i can't skip it.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000