Use INSERT INTO with multiple SELECT statements

Question

I am trying to insert several rows into a table. Almost all of the values for these rows match up with another set of records on the same table, so I am curious if I can write a single INSERT statement with multiple SELECT statements to insert multiple rows. Something like this:

INSERT INTO table (col1, col2, col3, col4)

SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 0
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 1
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 2
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 3

Is this possible? At the end, it should have inserted 4 rows, all with the same NewRecordID, with col4 values 0-3, and the other col values taken from the corresponding OldRecordID on the same table. When I run it, it just runs the first INSERT SELECT statement and then runs the others as their own SELECT statements. Am I missing something in connecting these? I could always just run it multiple times and change the value each time, but I'm wondering if there is a way to avoid that. I appreciate and help or useful tips.

Thanks

Answer 1

You could connect with Union which joins them into one recordset:

INSERT INTO table (col1, col2, col3, col4)

SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 0
union
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 1
union
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 2
union
SELECT (newRecordID, col2, col3, col4)
FROM table WHERE col1 = oldRecordID AND col4 = 3