Insert with Nested Select - Syntax Error

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 2005 Forums

Transact-SQL (2005)

Insert with Nested Select - Syntax Error

Author

Topic

krisdotca
Starting Member

6 Posts

Posted - 2010-04-27 : 10:36:32

Hi All,

I'm trying to construct an insert statement with a nested select but keep receiving an error stating that my syntax is incorrect. I'm sure it's something to do with the way that I'm nesting the subquery. Can someone point me in the right direction? Cheers!

INSERT INTO Appointments (
AppointmentId, AppointmentFacilityId, AppointmentDate, AppointmentType, IsConfirmed, AppointmentNotes, AppointmentUserId)
SELECT NEWID(),
SELECT FacilityId FROM Facilities WHERE FacilityName = 'FacilityName',
'01/01/2008 13:00', 'Blah',0,NULL,
SELECT UserId FROM dbo.aspnet_Users WHERE username = 'patient'
GO

The error states:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 10:44:02

[code]INSERT INTO Appointments (
AppointmentId, AppointmentFacilityId, AppointmentDate, AppointmentType, IsConfirmed, AppointmentNotes, AppointmentUserId)

SELECT NEWID(),
(SELECT FacilityId FROM Facilities
WHERE FacilityName = 'FacilityName'),
'01/01/2008 13:00', 'Blah',0,NULL,
(SELECT UserId FROM dbo.aspnet_Users WHERE username = 'patient')
GO
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 10:50:41

it should be


INSERT INTO Appointments (
AppointmentId, AppointmentFacilityId, AppointmentDate, AppointmentType, IsConfirmed, AppointmentNotes, AppointmentUserId)
SELECT NEWID(),
(SELECT FacilityId FROM Facilities WHERE FacilityName = 'FacilityName'),
'01/01/2008 13:00', 'Blah',0,NULL,
(SELECT UserId FROM dbo.aspnet_Users WHERE username = 'patient')

i cant understand meaning of this though. whats the use of populating some unrelated values? also if any of subqueries return more than 1 value then above will fail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

krisdotca
Starting Member

6 Posts

Posted - 2010-04-27 : 12:16:05

Thanks ms65g and visakh16 - the extra brackets did the trick. Visakh16, to answer your question about the meaning, I modified the original query to put dummy data in it. In the actual application the FacilityName and username will be unique so there's no chance of a duplicate occurring.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:21:15

ok..cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources