Insert constant value in table using sql script

Question

I am transferring data from one table to another table using sql script. In my parent table I have certain columns which have constants declared. I want that, instead of transferring the constant's value, their name should be transferred instead. Is it possible using sql script?

PARENT TBL

ID | NAME | COLOR_ID
2  | test | 1

I just want color_id to be changed to Green via sql script.

RECEIVING TBL

ID | NAME | COLOR
2  | test | Green

Script

Insert in receiving_tbl(id, name, color) select (id, name, color_id) from parent_tbl

Where the color name are defined? There is another table I think — Serpiton
– Serpiton, Commented Jul 15, 2014 at 19:22
@Serpiton This is the problem, they are application constants but i want them to be transferred as name to other table. Certainly, i would be required to declare them but how? — Femme Fatale
– Femme Fatale, Commented Jul 15, 2014 at 19:24

Serpiton · Accepted Answer · 2014-07-15 19:30:25Z

2

If the color name are not defined in the database the only option is to define a CASE to translate the ID to the names, something like

INSERT INTO receiving_tbl(id, name, color) 
SELECT id, name
     , CASE color_id WHEN 1 THEN 'Green'
                     WHEN 2 THEN 'Red'
                     ...
                     ELSE NULL
       END 
FROM parent_tbl

answered Jul 15, 2014 at 19:30

Serpiton

3,6843 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2858650 · Accepted Answer · 2014-07-15 19:30:51Z

2

If you have a table with your color ids and names stored, you can do this easily by including a join to your "name" table.

If, however, you do not have this information stored in a table, you can create this type of logic by using a case statement.

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

answered Jul 15, 2014 at 19:30

user2858650

Collectives™ on Stack Overflow

Insert constant value in table using sql script

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related