Solved

TSQL - Nested a case statment to insert values into a table

Posted on 2011-03-08
3
485 Views
Last Modified: 2012-06-21
I'm working on an insert / select statement for migrating data from one database to another.

The host DB has a flat table structure so I need to select a field which could be A, B or C for example and somehow convert this value so that A = 1, B = 2 and C= 3 in the destination table.

My script uses an insert and selecr statement to import the data? Being a newcomer to TSQL i', not sure what the best way to handle this would be? e.g Case Statement imbedded in the select clause?
0
Comment
Question by:mbs2000
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35068108
you have 2 options, roughly:
hard-code the "translation":
select case when FIELD = 'A' THEN 1 WHEN field = 'B' THEN 2 ... etc END  

Open in new window


or create a mapping table with the translations:
 create table tblMapping1 (field_value varchar(100), value_replacement varchar(100));
insert into tblMapping1 values ('A', '1');
insert into tblMapping1 values ('B', '2');
etc 

Open in new window


and use that table:
select t.field, m.value_replacement
  from yourtable t
  left join tblMapping1 m
   on m.field_value = t.field

Open in new window


hope this helps
0
 
LVL 18

Expert Comment

by:deighton
ID: 35068122
SELECT ShipVia, CASE ShipVia
    WHEN 1 THEN 'A.Datum'
    WHEN 2 THEN 'Contoso'
    WHEN 3 THEN 'Consolidated Messenger'
        ELSE 'Unknown'
        END AS MT FIELD
FROM Orders
0
 
LVL 6

Expert Comment

by:jonaska
ID: 35068367
INSERT INTO myImportTable (myDestCol)
SELECT
CASE MySrcCol
 WHEN 'A' THEN 1
 WHEN 'B' THEN 2
 WHEN 'C' THEN 3
END AS MySrcCol

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now