SQL - Insert Using Column Name as Value

Question

I am trying to do an insert into table2 based on a select from table1, but I can not get the correct syntax. The column names from table1 will drive the value being inserted into the PD_NO column in table2, as shown in the example below. Can anyone help with this?

Table1:

         (1)     (2)     (3)     (4)     (5)     (6)
| SEQ | PD_01 | PD_02 | PD_03 | PD_04 | PD_05 | PD_06 |
|-----+-------+-------+-------+-------+-------+-------|
| 632 | 10000 |   0   |  500  |   0   | 20000 |   0   |

Table2:

| SEQ | PD_NO |  AMT  |
|-----+-------+-------|
| 632 |   1   | 10000 |
|-----+-------+-------|
| 632 |   3   |  500  |
|-----+-------+-------|
| 632 |   5   | 20000 |
|-----+-------+-------|

I know if I am working the other direction (inserting contents of table2 into table1) that I can do something like the following:

INSERT INTO table1
SELECT 
      seq,
      SUM (CASE WHEN pd_no = 1 THEN amt ELSE 0 END) p01_amt,
      SUM (CASE WHEN pd_no = 2 THEN amt ELSE 0 END) p02_amt,
      SUM (CASE WHEN pd_no = 3 THEN amt ELSE 0 END) p03_amt,
      SUM (CASE WHEN pd_no = 4 THEN amt ELSE 0 END) p04_amt,
      SUM (CASE WHEN pd_no = 5 THEN amt ELSE 0 END) p05_amt,
      SUM (CASE WHEN pd_no = 6 THEN amt ELSE 0 END) p06_amt
FROM table2;

Please tag your question with the brand of RDBMS you're using. E.g. `sql-server`, `mysql`, `oracle`, etc. — Bill Karwin, Jul 12 '13 at 19:10

score 4 · Accepted Answer · answered Jul 12 '13 at 22:26

This is a typical problem for which Oracle 11 provides UNPIVOT clause for use in queries:

insert  into table2(seq, pd_no, amt)
select  seq, pd_no, amt
from    ( select  *
          from    table1
          unpivot (amt for pd_no in (pd_01 as 1, pd_02 as 2, pd_03 as 3, pd_04 as 4, pd_05 as 5, pd_06 as 6))
        );

score 2 · Answer 2 · answered Jul 12 '13 at 19:06

In pure sql it can be done in this way:

INSERT INTO table2 ( SEQ , PD_NO,  AMT )
SELECT SEQ, 1 as pd_no, PD_01 FROM Table1
UNION ALL
SELECT SEQ, 2 as pd_no, PD_02 FROM Table1
UNION ALL
SELECT SEQ, 3 as pd_no, PD_03 FROM Table1
UNION ALL
SELECT SEQ, 4 as pd_no, PD_04 FROM Table1
UNION ALL
SELECT SEQ, 5 as pd_no, PD_05 FROM Table1
UNION ALL
SELECT SEQ, 6 as pd_no, PD_06 FROM Table1

Some databases have optimized commands that read source table only once (the above query reads source table 6 times), for example in ORACLE:

INSERT ALL
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 1, PD_01 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 2, PD_02 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 3, PD_03 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 4, PD_04 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 5, PD_05 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 6, PD_06 )
SELECT * FROM table1

score 0 · Answer 3 · answered Jul 12 '13 at 18:59

insert ... select from works on a 1:1 row basis: one row of data from the 'select' table goes into one row of the 'insert' table. You're trying to take one row from the source table and turn it into multiple rows in the target table. This is not directly possible. You'll have to run multiple insert/selects, one for each field you want to split out:

INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_01, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_02, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_03, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_04, AMT FROM table1
etc...

Bill Karwin · Answer 4 · 2013-07-12T19:41:22.930

0

CREATE TABLE 1to6 (i INT PRIMARY KEY);
INSERT INTO 1to6 (i) VALUES (1), (2), (3), (4), (5), (6);

INSERT INTO table2 (seq, pd_no, amt)
SELECT seq, i, 
 CASE(i)
 WHEN 1 THEN pd_01
 WHEN 2 THEN pd_02
 WHEN 3 THEN pd_03
 WHEN 4 THEN pd_04
 WHEN 5 THEN pd_05
 WHEN 6 THEN pd_06
 END
FROM table1 CROSS JOIN 1to6

Re comment: Apparently Oracle doesn't support multi-row INSERT syntax (despite that syntax being standard SQL-99). You can insert one row at a time:

INSERT INTO 1to6 (i) VALUES (1);
INSERT INTO 1to6 (i) VALUES (2);
INSERT INTO 1to6 (i) VALUES (3);
INSERT INTO 1to6 (i) VALUES (4);
INSERT INTO 1to6 (i) VALUES (5);
INSERT INTO 1to6 (i) VALUES (6);

edited Jul 12 '13 at 19:41

answered Jul 12 '13 at 19:07

Bill Karwin

538,548
86
673
828

This option seems very clean. However, I am getting an "SQL command not properly ended" error when trying to insert the values on line 2. Removing the ", (2), (3), etc.." works fine and gives me a table with one column (i) and a value of 1. Any thoughts? – thefreeline Jul 12 '13 at 19:24

SQL - Insert Using Column Name as Value

4 Answers4