Insert using select statements

• Mr North

  SSC Veteran

  Points: 217

  More actions

  December 12, 2019 at 2:50 pm

  #3707409

  I'm writing my first sproc but I don't understand how to insert from two tables in one statement

   

  CREATE PROCEDURE Orders_PO_Insert 
       @jobid INT, 
       @accountnumber NVARCHAR(255),
       @siteid INT, 
       @serviceid INT
  AS
      DECLARE @orderid INT
  
  
      INSERT INTO dbo.orders (projectid, accountnumber, jobid, siteid, 
                              siteaddress1, siteaddress2, siteaddress3, siteaddress4, sitepcode,
                              requiredby, ordereddate, contactname, contactphone, contactemail)
      VALUES (
              (SELECT projectid FROM jobs WHERE id = @jobid),
              @accountnumber, @jobid,@siteid,
              (SELECT TOP (1) SiteAdd1, SiteAdd2, SiteAdd3, SiteAdd4, sitepostcode FROM SiteAddresses WHERE siteid = @siteid),
              (SELECT TOP (1) edd, jobadded, ContactName, ContactPhoneNum, ContactEmail FROM jobs WHERE id = @jobid)
             )
  
      SET @orderid = SCOPE_IDENTITY()
  
      INSERT INTO orderitems (orderid, unitcost)
      VALUES (@orderid,
                 (SELECT cost FROM temp_billing WHERE serviceid = @serviceid)
             )

  • This topic was modified 4 years, 11 months ago by  Mr North.

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  December 12, 2019 at 3:03 pm

  #3707417

  You don't need to use VALUES when selecting from one or more tables.

  INSERT t(col1, Col2)

  SELECT t1.Col1, t2.Col2

  FROM t1 join t2 ...

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 12, 2019 at 3:05 pm

  #3707418

  Which part of this are you having trouble with - inserting into orders from the two tables jobs and SiteAddresses in a single statement, or inserting into the two tables orders and orderitems in a single statement?

  By the way, SELECT TOP(1) is meaningless if you don't have an ORDER BY clause.  And, assuming id and siteid uniquely identify rows in jobs and SiteAddresses respectively, they're also unnecessary, since you'll only ever get a single row from those SELECT statements.

  John

• Steve Collins

  SSCrazy

  Points: 2160

  More actions

  December 12, 2019 at 3:27 pm

  #3707429

  Is id the primary key of jobs?

  Is siteid the primary key of SiteAddresses?

  To get the columns from both tables onto a single row you could use CROSS JOIN.  It works if the tables (on both sides of the CROSS JOIN) are restricted to a single row.   If the query selects are being done on primary keys then top (1) is not necessary.  If there are dupes then maybe they're needed, idk.

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können