Doing multiple inserts with a subquery?
-
Hey, I hope this is something simple but I'm already over my head (but making progress ... yeayyyy). Anyway, here's a link to my previous thread (which contains the data schema and sample data):
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=335969
I've got the update part working but I am having a heck of a time getting the program to insert all newsletters for employees not currently in the newsletter list. Anyway, here is the query I'm trying to run:
insert into tblEmpLet
(
empID, nletID, nlfID
)
select distinct e.empID, (SELECT el.nletID, el.nlfID
FROM tblEmpLet
WHERE empID = @in_intEmpID)
from tblEmployee e
left join tblEmpLet el on e.empID = el.empID
where el.empID is null
but it gives me this error:
"The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns"
I'm don't understand that part because I thought I could use a subquery to get the last 2 columns. Obviously not.
What I am trying to accomplish is this: I pass an employeeID (@in_intEmpID). For every employee of the same company that person belongs to, I want to add them to tblEmpLet and give them the same newsletters subscriptions as the employee whom I specified. For example, I pass Ali Larters employeeID. Evangeline Lilly, employee of the same company, only has 1 newsletter that she receives. I want to add to her collection any newsletters that Ali Larter receives that she, Evangeline Lilly, does not. Also match the nlfID (newsletter format).
I've got the Update part working. That part can be found in the link that I posted above. But I can't wrap my head around how to loop through one employees collection and add each of those to another employee.
Thank you in advance for any and all help that you can give. It is much appreciated.
-
Work through it in steps.
How do you find the other employees of the same company ? Need to join tblEmployee to itself:
Select e1.EmpID, e2.EmpID As OtherEmployeeID
From tblEmployee As e1
Inner Join tblEmployee As e2
On (e1.CompID = e2.CompID)
Where e1.EmpID = @in_intEmpID
Join this to existing subscriptions - these are the letter subscriptions that the 'Other' employees need to get:
Select l.nLetID, l.nlfID, e2.EmpID As OtherEmployeeID
From tblEmpLet As l
Inner Join tblEmployee As e1
On (l.EmpID = e1.EmpID)
Inner Join tblEmployee As e2
On (e1.CompID = e2.CompID)
Where e1.EmpID = @in_intEmpID
Take that result set and INSERT it, using a NOT EXISTS to only INSERT where the rowset does not currently exist:
insert into tblEmpLet
(nletID, nlfID, empID)
Select l1.nLetID, l1.nlfID, e2.EmpID As OtherEmployeeID
From tblEmpLet As l1
Inner Join tblEmployee As e1
On (l1.EmpID = e1.EmpID)
Inner Join tblEmployee As e2
On (e1.CompID = e2.CompID)
Where e1.EmpID = @in_intEmpID
And Not Exists (
Select *
From tblEmpLet As l2
Where l2.empID = e2.empID
And l2.nletID = l1.nletID
)
-
Thank you for that. It worked like a charm. I can definitely say I don't think I would have figured that one out anytime soon. Thank you especially for posting the steps and how you got to the final result. That definitely helps me understand that you're doing and why. Very cool!
Now at least I won't be banging my head against the wall all weekend
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply