SQL: Copy one row multiple times, changing one value each time

Question

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.

@astander: Consider the 'new keys' table to be the foreign key table (it's actually just a list of values sent to me in an Excel spreadsheet.) — Malvineous, Oct 23 '12 at 04:14

score 4 · Accepted Answer · answered Oct 23 '12 at 04:06

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.

INSERT INTO table (Key, Val1, Val2)
SELECT d.newKey, t.Val1, t.Val2
FROM table t
cross join (select 2 NewKey from dual union all
            select 3 NewKey from dual union all
            select 4 NewKey from dual) d;

If your new keys come from a subquery, it'd be even easier, e.g.

INSERT INTO table (Key, Val1, Val2)
SELECT d.FKey, t.Val1, t.Val2
FROM table t
cross join (select FKey
            from SomeOtherTable
            Where ......) d;

However, do note that because you're copying FROM table and INTO table, you'll replicate ALL the records from table at the same time for each foreign key.

Fantastic, this works perfectly! Many thanks. I didn't know about 'cross join'. — Malvineous, Oct 23 '12 at 04:15

SQL: Copy one row multiple times, changing one value each time

1 Answers1