Post reply

USING SUBQUERY IN INSERT STATEMENT

  • January 6, 2009 at 11:10 am

    #383691

    INSERT INTO tblProject

    (ProjectName, Description, RequestedBy, DeptID)

    VALUES (@ProjectName,@Description,@RequestedBy,(select deptID from Department where Department=@Dept) as @DeptID)

    The error I get is:

    Subqueries are not allowed in this context. Only scalar expressions are allowed

  • January 6, 2009 at 11:12 am

    #921982

    Do you have a question about the code?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 6, 2009 at 11:18 am

    #921989

    The error I get is :

    Subqueries are not allowed in this context. Only scalar expressions are allowed.

  • January 6, 2009 at 11:21 am

    #921993

    Franco_1 (1/6/2009)

    VALUES (@ProjectName,@Description,@RequestedBy,(select deptID from Department where Department=@Dept) as @DeptID)

    The source for an insert can be either VALUES or SELECT, not a combination.

    Try this:

    INSERT INTO tblProject (ProjectName, Description, RequestedBy, DeptID)

    SELECT @ProjectName,@Description,@RequestedBy, deptID

    FROM Department

    WHERE Department=@Dept

    Edit: Typo in select

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 6, 2009 at 11:24 am

    #921994

    You can't set variables within a statement like that. You could set the variable prior to the insert statement or, just do this:

    INSERT INTO tblProject

    (ProjectName, Description, RequestedBy, DeptID)

    SELECT @ProjectName,@Description,@RequestedBy,DeptID

    FROM Department where Department =@Dept

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 6, 2009 at 12:47 pm

    #922071

    Thanks.

    It works when I use the INSERT and SELECT (as you suggested) in SQL Server Management Studio query pane.

    Visual Studio will not allow me to create a table adapter when I replace the VALUES with SELECT.:crying:

    Is it because @ProjectName,@Description,@RequestedBy is not part of the Department table?

    In SQL Server Management Studio query pane, I had to actually pass in string values for it to work, not variables.

  • January 6, 2009 at 1:07 pm

    #922102

    I'm sure you can use a stored procedure. Just pass the parameters to one.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 6, 2009 at 1:11 pm

    #922106

    I guess

  • January 6, 2009 at 1:25 pm

    #922123

    Franco_1 (1/6/2009)

    Is it because @ProjectName,@Description,@RequestedBy is not part of the Department table?

    In SQL Server Management Studio query pane, I had to actually pass in string values for it to work, not variables.

    The variables will work, as long as they've been declared before the insert.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply