How to run sub-queries from an INSERT statement?

Question

SQL 2005

I have a table called tPieceTable whose layout is like the following:

ID   BatchNo    PieceNo
-----------------------
1    abc        123
2    xyz        321

In tPieceTable, "ID" is an IDENTITY(1,1).

I also have another table (tLiveTable) which has the following layout.

ID   TimeStamp  LocationRef
---------------------------

What I am trying to do is create a stored procedure which will INSERT the ID from tPieceTable INTO tLiveTable WHERE the BatchNo is equal to @BatchNo AND the PieceNo is equal to @PieceNo (these are parameters to the stored procedure).

I have the following which appears to be working:

CREATE PROCEDURE spSetLocationChargeTable
  @BatchNo nvarchar(50),
  @PieceNo nvarchar(50)
AS
  INSERT INTO tLiveTable(PieceID)
  SELECT ID FROM tPieceTable
  WHERE tPieceTable.BatchNo = @BatchNo AND tPieceTable.PieceNo = @PieceNo
GO

Now, at the same time as the INSERT, I want to update the TimeStamp for the new record as GetDate(), and for LocationRef I want insert the output of the following query:

SELECT ID FROM tLocationRefs WHERE Equipment = 'CHARGE_TABLE'

For reference tLocationRefs looks like this:

ID   Equipment     Description
------------------------------
0    CHARGE_TABLE  Charger Machine Table

Where I am struggling is how combine all this into one INSERT. I have tried doing an insert followed by two UPDATEs, but I cannot reference the tPieceTable, e.g. with the following:

CREATE PROCEDURE spSetLocationChargeTable
  @BatchNo nvarchar(50),
  @PieceNo nvarchar(50)
AS
  INSERT INTO tLiveTable(PieceID)
  SELECT ID FROM tPieceTable
  WHERE tPieceTable.BatchNo = @BatchNo AND tPieceTable.PieceNo = @PieceNo

  UPDATE tLiveTable
  SET TimeStamp = GetDate()
  WHERE tPieceTable.BatchNo = @BatchNo AND tPieceTable.PieceNo = @PieceNo

  UPDATE tLiveTable
  SET LocationRef = (SELECT ID FROM tLocationRefs WHERE Equipment = 'CHARGE_TABLE')
  WHERE BatchNo = @BatchNo AND PieceNo = @PieceNo
GO

I get :-

"The multi-part identifier "tPieceTable.BatchNo" could not be found." "The multi-part identifier "tPieceTable.PieceNo" could not be found."

Thanks to all for any assistance.

Andomar · Accepted Answer · 2012-12-03 15:32:41Z

1

INSERT  INTO tLiveTable
        (PieceID, TimeStamp, LocationRef)
SELECT  ID 
,       getdate()
,       (
        SELECT  ID 
        FROM    tLocationRefs 
        WHERE   Equipment = 'CHARGE_TABLE'
        )
FROM    tPieceTable  
WHERE   tPieceTable.BatchNo = @BatchNo
        AND tPieceTable.PieceNo = @PieceNo

answered Dec 3, 2012 at 15:32

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to run sub-queries from an INSERT statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related