• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

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

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
mbs2000
Asked:
mbs2000
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
deightonCommented:
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
 
jonaskaCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now