Insert with a Subquery Question: Error Received? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Insert with a Subquery Question: Error Received?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-12 : 12:01:21

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): [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77319[/url].

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:

quote:
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.

DTFan
Ever-hopeful programmer-in-training

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-12 : 16:18:04

What are you wanting now that wasn't previously answered here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=77170

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources