CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle12c
Hareesh
Hareesh

Reputation: 734

Insert ALL from a another table

I am trying to execute the below statement in one shot. Like we do it using INSERT ALL for inserting rows in a single execution. Is there any way that we can execute the below two statements in a single execution (using INSERT ALL).

INSERT
INTO TABLE_a
  (
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
  )
SELECT 1234, -- unique id for this insertion
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
FROM TABLE_B
WHERE COLUMN_9=1;
INSERT
INTO TABLE_a
  (
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
  )
SELECT 1235, -- unique id for this insertion
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
FROM TABLE_B
WHERE COLUMN_9=2;

Upvotes: 0

Views: 95

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Why not use OR condition ? Try this if it works for you

INSERT
INTO TABLE_a
  (
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
  )
SELECT CASE WHEN COLUMN_9 = 1 THEN 1234 WHEN COLUMN_9 = 2 THEN 1235 END,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
FROM TABLE_B
WHERE COLUMN_9=1 OR COLUMN_9=2;

Using DECODE() function as suggested by @Chris

INSERT
INTO TABLE_a
  (
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
  )
SELECT DECODE(COLUMN_9,1,1234,2,1235)
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
FROM TABLE_B
WHERE COLUMN_9=1 OR COLUMN_9=2;

Upvotes: 0

Ori Marko
Ori Marko

Reputation: 58772

Of course, you can use in clause to collect both Id 1 and 2

and use decode to differ between 1234 and 1235:

INSERT
INTO TABLE_a
  (
    COLUMN_1,
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
  )
SELECT decode(COLUMN_9,1,1234,2,1235),
    COLUMN_2,
    COLUMN_3,
    COLUMN_4,
    COLUMN_5,
    COLUMN_6,
    COLUMN_7,
    COLUMN_8
FROM TABLE_B
WHERE COLUMN_9 in (1,2);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

You may use INSERT INTO ... SELECT with a union query:

INSERT INTO TABLE_a (COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6,
    COLUMN_7, COLUMN_8)
SELECT
    1234, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6, COLUMN_7, COLUMN_8
FROM TABLE_B
WHERE COLUMN_9 = 1
UNION ALL
SELECT
    1235, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6, COLUMN_7, COLUMN_8
FROM TABLE_B
WHERE COLUMN_9 = 2

This approach has one noticeable advantage over your original approach. With this answer, the entire insert is happening in a single statement.

Upvotes: 0

Related Questions