How do I add MAX(RowID)+1 when adding multiple records?

Question

Tables:

1. Orders -- this stored RowID
2. ExternalOrders -- this is where I get some important order data from

I'm trying to add orders into the Orders table using data from ExternalOrders. However, I want to add multiple records at the same time and ensure that a new RowID number gets assigned to each new row.

For example, if the last RowID that currently exists in the Orders table is 1 then let the next row have a RowID of 2, then 3, 4...etc.

I've tried the following code:

CREATE TABLE Orders
(
    RowID BIGINT,
    OrderID VARCHAR(200)
)

INSERT INTO Orders (RowID, OrderID)
    SELECT 
        MAX(O.RowID) + 1,
        E.Order_ID
    FROM 
        [DatabaseName].[dbo].[ExternalOrders] E
    LEFT JOIN 
        Orders O ON O.SaleDate = E.Sale_Date

The error I get is:

Msg 8120, Level 16, State 1, Line 9
Column 'DatabaseName.dbo.ExternalOrders.Order_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I also tried using the following as RowID:

DECLARE @NewRowID INT = (SELECT MAX(RowID) FROM Orders) + 1

but this makes all records I insert have the same new RowID.

Important: there are already records in the Orders table so I want to resume RowID numbering from the last one present.

Answer 1

You should likely be using an IDENTITY column for your auto incrementing ID. You do not provide a value for these columns when inserting into the table.

DECLARE @Orders TABLE (RowID INT IDENTITY, OrderDate DATETIME, CustomerID INT)

INSERT INTO @Orders (OrderDate, CustomerID) VALUES
(GETUTCDATE(), 1), (DATEADD(HOUR,-1,GETUTCDATE()), 2)

SELECT *
  FROM @Orders

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver16