can I have subquery in INSERT statement

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  November 11, 2003 at 1:54 pm

  #101753

  Howdy folks,

  The following statement runs when hard-coded with a number:

  INSERT INTO tblProduct

  (productId, productCatId)

  VALUES

  (@productId, 2)

  however, I'd like the second value to be the result of a subquery, as follows:

  INSERT INTO tblProduct

  (productId, productCatId)

  VALUES

  (@productId, SELECT prodCatId FROM tblProductCategory WHERE columNameValue= @columNameValue)

  ..I get error "Incorrect syntax near the keyword 'SELECT'"

  I have correctly initialised all the parameters that I passed.

  Can anyone point me in the right direction?

  Many thanks,

  yogi

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  November 11, 2003 at 2:15 pm

  #481623

  
  
  INSERT INTO tblProduct (productId, productCatId)
  
  SELECT @productId, prodCatId
  
  FROM tblProductCategory
  
  WHERE columNameValue = @columNameValue

  --Jonathan

  
  
  --Jonathan

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  November 11, 2003 at 2:32 pm

  #481624

  Cheers Jonathan,

  It seems that it's better to write insert queries the way you've done it.Was the way I was doing it an older style?

  Ta,

  yogi

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  November 12, 2003 at 9:48 am

  #481748

  No Yogiberr.

  It is not an older stíle, it is just and another syntax.

  If you check the BOL you can see that EITHER you assign values OR you assign select results to a table.

  Bye

  Gabor

  
  
  Bye
  Gabor

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  November 12, 2003 at 2:47 pm

  #481778

  Hi Gabor,

  thanks bud, i think i'll stick to the "SELECT subquery" version.

  cheers,

  yogiberr