Using a sub-query in an SQL insert statement

Question

IF NOT EXISTS (SELECT TOP 1 CityName FROM dbo.City WHERE [Name] = 'Default City')
BEGIN
INSERT dbo.City ( CityName, Status,CityCategoryId) VALUES  
(N'Default City', 0, (SELECT CityCategoryId FROM dbo.CityCategory WHERE [CityCategoryName] = N'Default City Category'))
END
GO

i have the the above query which runs fine in SQL server 2012 but fails in SQL Server 2005 with the below error message.

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

if N'Default City', 0 are hardcoded values use it in the select statement itself bu removing the keyword VALUES — Krish KvR
– Krish KvR, Commented Nov 17, 2014 at 6:42
This feature was introduced in SQL Server 2008. Hence, in the version below than this you need to declare variable and assign the value to variable and use it in INSERT statement. — Paresh J
– Paresh J, Commented Nov 17, 2014 at 6:50

Pரதீப் · Accepted Answer · 2014-11-17 06:46:13Z

1

Change your insert like this.

INSERT dbo.City
       (CityName,Status,CityCategoryId)
SELECT N'Default City',0,CityCategoryId
FROM   dbo.CityCategory
WHERE  [CityCategoryName] = N'Default City Category'

answered Nov 17, 2014 at 6:46

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Veera Over a year ago

If there is no CityCategoryId for the select query. Then the values N'Default City',0 won't get inserted.

Veera · Accepted Answer · 2014-11-17 06:54:17Z

1

Try using variable @CityCategoryId. I don't know the type of @CityCategoryId. You can use datatype that you want.

IF NOT EXISTS (SELECT TOP 1 CityName FROM dbo.City WHERE [Name] = 'Default City')
BEGIN
    Declare @CityCategoryId AS NVARCHAR(100)
    SELECT @CityCategoryId = CityCategoryId FROM dbo.CityCategory 
           WHERE [CityCategoryName] = N'Default City Category'

    INSERT dbo.City( CityName, Status, CityCategoryId) 
    VALUES  
        (N'Default City', 0, @CityCategoryId)
END
GO

edited Nov 17, 2014 at 6:54

answered Nov 17, 2014 at 6:48

Veera

3,4922 gold badges16 silver badges27 bronze badges

Comments

Krish KvR · Accepted Answer · 2014-11-17 06:46:44Z

0

Try like this. Am not sure...

IF NOT EXISTS (SELECT TOP 1 CityName FROM dbo.City WHERE [Name] = 'Default City')
BEGIN
INSERT dbo.City ( CityName, Status,CityCategoryId)  
SELECT 'Default City',0, CityCategoryId FROM dbo.CityCategory WHERE [CityCategoryName] = N'Default City Category'
END
GO

answered Nov 17, 2014 at 6:46

Krish KvR

1,0544 gold badges11 silver badges18 bronze badges

2 Comments

Veera Over a year ago

If there is no CityCategoryId for the select query. Then the values N'Default City',0 won't get inserted.

Krish KvR Over a year ago

Yea that's true.. Good find Bro.. I didn't noticed that

Collectives™ on Stack Overflow

Using a sub-query in an SQL insert statement

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related