Function like vlookup to find and replace value

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

Transact-SQL (2008)

Function like vlookup to find and replace value

Author

Topic

Tuan
Starting Member

2 Posts

Posted - 2014-09-26 : 06:15:23

Hi,

I am new to SQL server and I've been tasked to transfer some data from Excel to SQL, with some logics to be performed in SQL. One issue that I haven't been able to solve is to use a vlookup-like function in SQL.

I have a fact table and a dimension table. Format is shown as follows.

Fact table:
ID Name Desc Region
1 a xxxx UK
2 b yyyy US
3 c zzzz US
4 d aaaa China

Dimension table:
Region_ID Region_Name
1 US
2 UK
3 China
4 Japan

What I'd like to achieve is to do a look-up check and replace the regions in the fact table with region IDs, according to the dimension table. Can someone please help me out on this? Thanks in advance.

Regards,
Tuan

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-09-26 : 07:16:48

In T-SQL you use a JOIN. To replace you use an UPDATE. Something like:

UPDATE F
SET Region = D.Region_ID
FROM Fact F
INNER JOIN Dimension D ON F.Region = D.Region_Name

djj

Tuan
Starting Member

2 Posts

Posted - 2014-09-26 : 07:40:37

Thanks a lot djj! Works perfectly now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources