Import from excel to more than one db table

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 2005 Forums

SSIS and Import/Export (2005)

Import from excel to more than one db table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-02-07 : 05:54:05

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

735 Posts

Posted - 2008-02-07 : 13:01:15

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

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-07 : 13:06:35

Do you really need the XRef table? Will you be associating a contact with multiple companies?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-02-08 : 18:22:25

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources