Single SELECT statement, INSERT range of values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Single SELECT statement, INSERT range of values

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2004-12-31 : 17:02:15

I have a query that returns values for INSERT for partner_id = 100:

INSERT INTO partner_email
SELECT t1.some_value, t2.some_value, 100
FROM table1 t1 INNER JOIN table2 t2
ON t1.something = t1.something
WHERE et1.email_name IN ('One','Two','Three')

This will INSERT three rows for partner_id = 100. Now, lets say I want to do the same 3-row INSERT for partner_id 100-125? The partner_id is in a unrelated table, so a join to table1 or table2 is not possible:

SELECT partner_id
FROM partner
WHERE partner_name IN ('partner_one', 'partner_two', 'partner_three')

partner_id
-----------
100
101
102
...

Is there a way to do this in one SELECT? Without a temp table?
Example:

-- Bad SQL
INSERT INTO partner_email
SELECT t1.some_value, t2.some_value, (SELECT partner_id FROM...)
FROM table1 t1 INNER JOIN table2 t2
ON t1.something = t1.something
WHERE et1.email_name IN ('One','Two','Three')

nathans
Aged Yak Warrior

938 Posts

Posted - 2004-12-31 : 17:20:49

Cross JOIN...

INSERT INTO partner_email
SELECT t1.some_id,
t2.some_id,
t3.partner_id
FROM table1 t1 INNER JOIN tabel2 t2
ON t1.something = t1.something CROSS JOIN table3 t3
WHERE et1.email_name IN ('One','Two','Three')
AND t3.partner_id IN (100,101,103) -- OR BETWEEN, etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources