subquerry in a insert statement RRS feed

  • Question

  • Hello, is it possible to use a subquerry in a insert statement? I need to perform the following task: I have 2 tables


    Item_ID/ ItemName
    lineID/Item_ID/ Val1/Val 2

    When i add a new item i need to
    1) add the new item to the table 1
    2) Retrieve the  item_ID assigned by the database (progressive identification number)
    3) Add the item values

    I would like to avoid to access the database 2 times. I am trying something like this
    INSERT INTO Table1   ItemName values   NameOfItem
    INSERT INTO Table2  Item_ID,Val1,Val2 values ( SELECT Item_ID from Table1 where ItemName ='NameOfItem', Value1,Value2)

    this with one call but i get a message that reads: 

    Subqueries are not allowed in this context. Only scalar expressions are allowed.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    • Moved by Tony Petrossian Wednesday, September 3, 2008 12:43 AM off topic (Moved from SQL Server Data Services (SSDS) - Getting Started to Off-Topic Posts (Do Not Post Here))
    Saturday, July 26, 2008 6:23 AM