importing excel to database problem

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)

importing excel to database problem

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-05-06 : 07:40:44

Hi,

I need to import data from excel to sql database. I have an excel that has a column called dept_name that is a string.
In the database I have a table called tb_depts with the following columns:
dept_id int, identity
dept_name nvarchar (200)

In addition I have another table called tb_extUsers with the following columns:
user_id int, identity
user_name nvarchar (200)
dept_id int

I need to import the excel into the table tb_extUsers however my problem is that the value in excel is the name of the department and not the department id and the column in tb_extUsers requires the id.
How can i insert the correct dept_id into tb_extUsers ?

Can someone please assist me?

Thanks

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-06 : 07:48:40

1. Import your excel into some Stage Table as it is.
2. Move the data into tb_extUsers table by joining tb_depts and tb_extUsers on dept_name.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-05-07 : 16:44:28

Or you could do a lookup for the dept_id using the dept_name, if you have that relationship maintianed in the sql side.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-05-10 : 11:32:58

hi,

thanks for the replies. How can i move the data from one table to the other?
Thanks

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-12 : 05:45:18

quote:
Originally posted by collie

hi,

thanks for the replies. How can i move the data from one table to the other?
Thanks

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

Insert into table1(columns)
select columns from table2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources