Solved

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

Posted on 2011-03-08
3
497 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

27 Experts available now in Live!

Get 1:1 Help Now