Subselects in an insert statement? - 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

General SQL Server Forums

New to SQL Server Programming

Subselects in an insert statement?

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-10-29 : 18:51:11

I'm trying to do a kind of weird insert statement in an asp.net page.

This is an example of what I'm trying to do, but when I fill in the values myself to test it, I get the errors I pasted below the insert statement:


insert into Export (exportName, profileID, storeID)
values (@name, (select profileID from [Profile] where pName=@pName), (select storeID from [Store] where storeName=@storeName))



Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

So does this mean I cannot use subqueries in insert statements? Or am I doing something wrong?

Any suggestions for a better way to do this?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-29 : 19:06:24

I think either one of these should work for you:

DECLARE @ProfileID INT
DECLARE @StoreID INT

select @ProfileID = profileID from [Profile] where pName=@pName
select @StoreID = storeID from [Store] where storeName=@storeName

insert into Export (exportName, profileID, storeID)
values (@name, @ProfileID, @StoreID)

-- OR

insert Export (exportName, profileID, storeID)
SELECT 
	@name, 
	(select profileID from [Profile] where pName=@pName) AS ProfileID, 
	(select storeID from [Store] where storeName=@storeName) AS StoreID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-30 : 03:01:29

Make sure that the query returns single value
Otherwise you may get differenct result or error

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources