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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

400 Posts

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

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

-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.

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.

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.

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.

Go to Top of Page

Posting Yak Master

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

Constraint Violating Yak Guru

400 Posts

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

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  
 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.03 seconds. Powered By: Snitz Forums 2000