CROSS JOIN SELECT - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CROSS JOIN SELECT

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-23 : 13:05:34

HI

I am currently using this statement

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (5, 7)

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
)c

If I wanted to add another Customer level to the query (number 8 for example)

I try this

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (5, 7, 8)

INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7
SELECT 8
)c

But get this error. Do the SELECT 7 and SELECT 8 have to be on the same line? if so how?

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'SELECT'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:10:22

it should be this


DELETE FROM ProductCustomerLevel 
WHERE CustomerLevelID IN (5, 7, 8)

INSERT ProductCustomerLevel 
(
ProductID,
CustomerLevelID
)
SELECT ProductID,
CustomerLevel
FROM dbo.Product p
CROSS JOIN (
SELECT 5 AS CustomerLevel UNION ALL
SELECT 7 UNION ALL
SELECT 8
)c

ie each record separated by UNION ALL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-23 : 13:13:32

THANKS!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:16:22

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources