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

Question

INSERT into abc."User" 

 

( UserName,Pass,Passalt,FirstName,LastName,Email,Mobile,CreateDate,StatusID,CountryID)

 

values

 

('testUser12',

 

'testUser',

 

'testUser',

 

'testUser',

 

'testUser',

 

'testUser',

 

'123213213',

 

'',

 

(SELECT StatusID from dbo."Status" where StatusName='Active'), 

 

(SELECT CountryID from dbo."Country" where CountryCode='PK'))

 

Which return the following error on SQL SERVER 2005 and does not return the specified ERRROR on SQL Server 2008. Can any body let me know what the Problem is . Is it SQL Serevr Limitation of version?

 

 

Msg 1046, Level 15, State 1,Line 12

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

Msg 102, Level 15, State 1, Line 13

Incorrect syntax near ')'.

Answer 1

For insert with subquery, you must not pass values, it have to be some thing like this:

insert into Table

select 'testUser12','testUser','testUser','testUser','testUser','testUser','123213213', '', (SELECT StatusID from dbo."Status" where StatusName='Active'), (SELECT CountryID from dbo."Country" where CountryCode='PK')

---

------------------------------------------------------------- Oracle OCA11g

Answer 2

In your case insert would look like:

INSERT into abc."User"
( UserName,Pass,Passalt,FirstName,LastName,Email,Mobile,CreateDate,StatusID,CountryID)

SELECT
 UserName = 'testUser12',
 Pass='testUser',
 Passalt='testUser',
 FirstName='testUser',
 LastName='testUser',
 Email='testUser',
 Mobile='123213213',
 CreateDate='',
 Stat.StatusID,
 Co.CountryID
FROM dbo.[Status] Stat, dbo."Country" Co
WHERE StatusName='Active' and CountryCode='PK'

Regards,

Akim 

 

 

 

Answer 3

INSERT into abc."User" 

( UserName,Pass,Passalt,FirstName,LastName,Email,Mobile,CreateDate,StatusID,CountryID)

values

('testUser12',

'testUser',

'testUser',

'testUser',

'testUser',

'testUser',

'123213213',

'',

(SELECT StatusID from dbo."Status" where StatusName='Active'), 

(SELECT CountryID from dbo."Country" where CountryCode='PK'))

One of your selects return more than 1 record. Replace Select with Select TOP 1 . It should help.

To AkimZ: Your solution may not work in case if one of tables does not contain records match criteria.

 

Igor