CODEWITHSUNDEEP
sqlsql-server-2008-r2
jlarson497
jlarson497

Reputation: 31

Use INSERT INTO with multiple SELECT statements

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

Upvotes: 3

Views: 9864

Answers (2)

Ricardo C
Ricardo C

Reputation: 2244

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

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

Upvotes: 2

Dr. Aaron Dishno
Dr. Aaron Dishno

Reputation: 1919

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

Upvotes: 6

Related Questions