How to get MAX serial ID for INSERT statement for SQL Server-CodePudding

I am currently creating an INSERT statement using a two-step process, which I am trying to condense into one step.

Right now I have a process that is tedious but works:

First, I run this query to get the next SeriesID:

SELECT MAX(serialNo)   1 FROM Table1

Let's say it returns 54. Then next, I manually type the 54 into the INSERT/SELECT:

INSERT INTO Table1
    SELECT 54, NULL, 1, 'NEW', GETDATE(), Table2.TrackingID
    FROM Table2 
    LEFT JOIN Table3 ON Table3.pid = Table2.pid
    LEFT JOIN Table1 ON Table1.CustID = Table1.CustID
    WHERE Table3.packageNumber = '123456'

This works, but it is incredibly annoying to have to do the first query and then change the serial number manually every time. So I am trying to incorporate the query into the INSERT, but I am having no luck:

INSERT INTO Table1
    SELECT (MAX(serialNo)   1), NULL, 1, 'NEW', GETDATE(), Table2.TrackingID
    FROM Table2 
    LEFT JOIN Table3 ON Table3.pid = Table2.pid
    LEFT JOIN Table1 ON Table1.CustID = Table1.CustID
    GROUP BY Table2.TrackingID, Table3.packageNumber
    HAVING Table3.packageNumber = '123456'

And this technically works as in doesn't throw an error, but instead of inserting the first column with 54 as I was hoping, it inserts it with a NULL.

Expected result:

   54|NULL|1|NEW|2022-06-10|98765

Actual result:

 NULL|NULL|1|NEW|2022-06-10|98765

How do I get the MAX serial number from Table1, so that I can use MAX(serialNO) 1 for the serial number in the next entry?

CodePudding user response：

I would consider using a sequence for this, but if you want to do it the way you're doing it, you need to put the full subquery in your SELECT:

INSERT INTO Table1
SELECT (select MAX(serialNo)  1 from Table1),NULL,1,'NEW',GETDATE(),Table2.TrackingID
  from Table2 
  left join Table3 on Table3.pid = Table2
  left join Table1 on Table1.CustID = Table1.CustID
GROUP BY Table2.TrackingID, Table3.packageNumber
HAVING Table3.packageNumber = '123456'

When you only have MAX(serialNo) it is getting the maximum after doing your joins and conditions, which is different than the maximum in the entire table.