I have a row of data in a table:
Key | Val1 | Val2
----+------+-----
1 | A | B
I would like to copy this row, but assign each new row a different key (actually a foreign key) from a list:
New keys
--------
2
3
4
This list can easily be obtained via a query. After the duplication, the table should look like this:
Key | Val1 | Val2
----+------+-----
1 | A | B
2 | A | B
3 | A | B
4 | A | B
So far, all I've come up with is this:
INSERT INTO table (Key, Val1, Val2) (
SELECT '2' AS Key, Val1, Val2 FROM table WHERE Key='1'
);
This works, but of course it only copies one row at a time. Is there a way to copy all the rows at once?
I'm using Oracle if it makes a difference.
You can use SELECT .. FROM DUAL to make up values, and you can use UNION ALL to combine multiple rows into a single result. There are various other ways to create a result set such as a Recursive Common Table Expression.
If your new keys come from a subquery, it'd be even easier, e.g.
However, do note that because you're copying
FROM table
andINTO table
, you'll replicate ALL the records fromtable
at the same time for each foreign key.