none
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 ')'.

    • Moved by Tom PhillipsModerator Tuesday, March 29, 2011 5:15 PM TSQL question (From:SQL Server Database Engine)
    Tuesday, March 29, 2011 2:01 PM

Answers

  • 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
    Tuesday, March 29, 2011 2:07 PM

All replies

  • 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
    Tuesday, March 29, 2011 2:07 PM
  • 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 

     

     

     



    Tuesday, March 29, 2011 2:30 PM
  • 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

    Tuesday, March 29, 2011 8:40 PM