Use function like vlookup to find and replace value in SQL

Question

Use function like vlookup to find and replace value in SQL

yutuanfeng0109

SSC Enthusiast

Points: 194
More actions
September 22, 2014 at 3:25 am

#290939

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22924 More actions · Answer 1

Hi Tuan,

welcome to the forums. What you're asking to do is theoretically straightforward but there are a couple of traps for the unwary. The principal difficulty is the difference in data types between the Region ID and the Region Name. The ID is stored as an integer but the name is stored as a character string. This means you'll have to modify your table before you can update the data. How new to SQL Server are you?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

yutuanfeng0109 SSC Enthusiast Points: 194 More actions · Answer 2

Hi, thanks for the reply. I understand the difficulty here now. As for my SQL knowledge, it's pretty basic, mainly about how to create/alter a table, add foreign/primary keys, and import data. Would it be sufficient to tackle this problem? Please let me know what I have to do/learn. Thanks!

Neil Burton SSC-Insane Points: 22924 More actions · Answer 3

If you can create and alter tables then you're halfway there. You'll need to do both to get this done. The most important thing you need to do is make sure you have recent backup before you start. If anything goes wrong you'll need to be able to restore your data. This is an area I can't help you in because it's not something I've done so it will definitely be worth reading up on it.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537