multiple select with UNION and

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

SQL Server Development (2000)

multiple select with UNION and

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-07-06 : 14:29:58

i have this query


INSERT INTO LEVCompaniesDepartment (UserID,DepartmentID) SELECT 422,170 UNION ALL SELECT 422,320 UNION ALL SELECT 422,520 where not EXISTS (select UserID from LEVCompaniesDepartment where UserID=422 and DepartmentID=170 UNION ALL SELECT 422,320 UNION ALL SELECT 422,520

what i did using union i wanted to inset in ONE STEP lots of DepartmentID to a user,but i need to check for each one ofthem if the cmbination of (UserID,DepartmentID) exist beacuse i have them both as PRIMARY KEYS!!

what can i ddo?
the above query is build "dynamicly" where i use a trick from an answer i got here to use union on a variable like @depid where it will
give ma a ready made query of all select to insert once
so what can i do?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-06 : 14:34:28

This is what I see


INSERT INTO LEVCompaniesDepartment (
	 UserID
	,DepartmentID) 
SELECT 422,170 UNION ALL 
SELECT 422,320 UNION ALL 
SELECT 422,520 
WHERE NOT EXISTS (select UserID 
		    from LEVCompaniesDepartment 
		   where UserID=422 and DepartmentID=170 
		UNION ALL 
		   SELECT 422,320 
		UNION ALL 
		   SELECT 422,520

and it makes no sense

Why don't you tell us what you to have happen

Also, read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-07-06 : 14:39:48

the part where the "WHERE NOT EXISTS ..." was added and doing the problem

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-06 : 14:45:50

[code]
insert into LEVCompaniesDepartment
(
UserID,
DepartmentID
)
select
a.UserID,
a.DepartmentID
from
(
select UserID = 422, DepartmentID = 170 union all
select UserID = 422, DepartmentID = 320 union all
select UserID = 422, DepartmentID = 520
) a
left outer join
LEVCompaniesDepartment b
on
a.UserID = b.UserID and
a.DepartmentID = b.DepartmentID
where
b.UserID is null and
b.DepartmentID is null

[/code]

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-07-06 : 15:39:33

thanks alot that what i needed

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources