SQL I want to duplicate record on insert

Question

Without using a while or forloop, is there a way to insert a record two or more times on a single insert?

Thanks

INSERT INTO TABLE2 ((VALUE,VALUE)
SELECT VALUE,VALUE FROM TABLE1 )) *  2

score 2 · Accepted Answer · answered May 04 '11 at 13:16

2

You would need to CROSS JOIN onto a table with 2 rows. The following would work in SQL Server.

INSERT INTO TABLE2 ((VALUE,VALUE)

SELECT VALUE,VALUE 
FROM TABLE1, (SELECT 1 UNION ALL SELECT 2) T(C)

If you have an auxilliary numbers table you could also do

SELECT VALUE,VALUE 
FROM TABLE1 JOIN Numbers ON N <=2

answered May 04 '11 at 13:16

Martin Smith

438,706
87
741
845

Also could do `select value, value from table1 t1 union all select value, value from table1 t2 ...`. This would have the advantage of possibly adding a filter to choose which records are inserted once and which records are inserted twice. – mellamokb May 04 '11 at 13:22
Good Point. Would mean the source table is scanned twice though. – Martin Smith May 04 '11 at 13:23
Actually, couldn't you dynamically determine how many records to include via something liks `SELECT Value, Value FROM Table1 JOIN Numbers ON N <= Value`? – mellamokb May 04 '11 at 14:15
@mellamokb - Yes. As here http://stackoverflow.com/questions/5808083/get-multiple-records-from-one-record/5808148#5808148 – Martin Smith May 04 '11 at 14:18

score 1 · Answer 2 · answered May 04 '11 at 13:17

1

--first create a dummy table with 2 records

INSERT INTO TABLE2 ((VALUE,VALUE)
SELECT VALUE,VALUE FROM TABLE1, dummytable ))

answered May 04 '11 at 13:17

bpgergo

15,669
5
44
68

Union all does duplicate record, but its not dynamic. Sometimes the record may need to be duplicated 3 or 4 times. Maybe all I can really use is a while loop? – tdjfdjdj May 04 '11 at 13:30
@user - You can use a numbers table as per the second suggestion in my answer. – Martin Smith May 04 '11 at 15:55

score 0 · Answer 3 · answered May 04 '11 at 13:46

This is not an elegant way, but could work easily. If you have a table with an high enough number of records you can do the cross join with a TOP clause

INSERT INTO TABLE2 
SELECT VALUE,VALUE FROM TABLE1 
cross join (select top 2 TABLE_DUMMY) as DUMMY

This works for MQ SqlServer, to let it work in other DBMS you should change the TOP with the keyword needed by your DBMS

SQL I want to duplicate record on insert

3 Answers3