USING SUBQUERY IN INSERT STATEMENT

Question

USING SUBQUERY IN INSERT STATEMENT

Franco_1

Say Hey Kid

Points: 663
More actions
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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Franco_1 Say Hey Kid Points: 663 More actions · Answer 2

The error I get is :

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Franco_1 Say Hey Kid Points: 663 More actions · Answer 5

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.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Franco_1 Say Hey Kid Points: 663 More actions · Answer 7

Franco_1

Say Hey Kid

Points: 663

January 6, 2009 at 1:11 pm

#922106

I guess

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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