CODEWITHSUNDEEP
sqlt-sql
BBauer42
BBauer42

Reputation: 3657

How to move AND transform data in SQL?

As a simple example, say I have two tables named Old and New. The Old table has only one column called Decision that is a string and contains only "Yes", "No", or "Maybe".

The New table is empty. How can I get all of the data from the Old table, transform it, and store it in the new able as an integer(or as int 0="N", 1="Y", and 2="Maybe").

Thanks.

Upvotes: 1

Views: 65

Answers (3)

Art
Art

Reputation: 5782

In addition - single statement query:

create table NewTable as 
select (case Decision when 'N' then 0 when 'Y' then 1 else 2 end) int_col
 from OldTable 
/

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

I assume you are going to have a FK on the new table?

Table YesNoMaybe
Byte ID PK
Value varchar(10)

0, N
1, Y
2, Maybe

Insert into New
Select ID
From YesNoMaybe 
JOIN Old
  ON Old.Decision = YesNoMaybe.[value]

Upvotes: 0

Daniel Kelley
Daniel Kelley

Reputation: 7737

insert into NewTable (Decision)
select case Decision when 'N' then 0 when 'Y' then 1 else 2 end
from OldTable

Upvotes: 4

Related Questions