Oracle中如何将列名与对应字段值一同插入目标表？

Hey Joe, I get why UNPIVOT wasn't working for you—it's built to turn columns into rows, which is the exact opposite of what you need here. Instead, we can directly construct an INSERT statement that maps each original column to two columns in your target table (one for the column name, one for its corresponding value).

Here's the straightforward SQL query you can use: #

INSERT INTO TargetTable (COL_A, COL_B, COL_C, COL_D, COL_E, COL_F, COL_G, COL_H)
SELECT 
    'col1' AS COL_A, col1 AS COL_B,
    'col2' AS COL_C, col2 AS COL_D,
    'col3' AS COL_E, col3 AS COL_F,
    'col4' AS COL_G, col4 AS COL_H
FROM Table1;

How this works: #

• We hardcode the original column names as string values (like 'col1') for the odd-numbered target columns (COL_A, COL_C, etc.)
• We pull the actual data values from Table1 for the even-numbered target columns (COL_B, COL_D, etc.)
• This will take every row from Table1 and insert a matching row into your target table with column names and values laid out horizontally exactly as you specified.

If you ever need to handle a dynamic number of columns down the line, you'd have to use dynamic SQL to build this query programmatically. But since your column list is fixed here, this static approach is clean and efficient.

内容的提问来源于stack exchange，提问作者joe