I am trying to execute multiple insert with single statement to achieve this I am using Insert into select statement. But I am facing when two columns have same value in insert. Error message that I am getting is ORA-00918: column ambiguously defined.
Query
INSERT INTO sample (
HOST,
TOTAL_PING,
TOTAL_UNAVAILABLE_PING
)
SELECT * FROM (
SELECT 'FR3158-73-1',
82,
82
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
)
Issue is there in first select statement where two values are 82 and 82, if I change one value to something works. I don't know how to make this work even if column values are same.
--- Updates ---
Table Definition
CREATE TABLE sample
(
ID NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
HOST VARCHAR2(15 BYTE),
TOTAL_PING INTEGER,
TOTAL_UNAVAILABLE_PING INTEGER,
ADDED_ON TIMESTAMP(6) DEFAULT systimestamp
);
In this case you don't need the subquery - as @Littlefoot showed. But if you did, with a more complicated scenario, you can avoid the error by aliasing the column expressions in the subquery:
INSERT INTO sample (
HOST,
TOTAL_PING,
TOTAL_UNAVAILABLE_PING
)
SELECT * FROM (
SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
)
/
2 rows inserted.
The problem is that the subquery on its own gets implied column aliases, derived from the values in the first branch of the query:
SELECT 'FR3158-73-1',
82,
82
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
'FR3158-73- 82 82
----------- ---------- ----------
FR3158-73-1 82 82
FR3158-76-2 80 10
The second and third columns are both called "82", which is the ambiguity the ORA-00918 is complaining about, from the outer select. If you add aliases that goes away:
SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
HOST TOTAL_PING TOTAL_UNAVAILABLE_PING
----------- ---------- ----------------------
FR3158-73-1 82 82
FR3158-76-2 80 10
so the outer query is no longer confused. Note that you only need the aliases in the first branch of the union (usually, anyway) - it doesn't hurt to have them in all branches, they'll just be ignored, but it saves a bit of typing if you're creating this manually. The actual alias names also don't matter in this case, they just have to be unique; specifically, they don't have to match the columns you're inserting into - but it makes it easier to follow if they do.
If you do it as @Littlefoot showed you don't have the intermediate result set select, so the derived names don't need to be evaluated (if they can be said to exist at all), so the ambiguity is not seen - it's purely positional.