How many rows can SQL Server process in a single INSERT statement into a table?

Question

To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them:

With NewOrders 
As
(   
    Select OrderID From Orders
    Except
    Select OrderID From FactOrders
)
Insert Into FactOrders(OrderID, OrderDate, CustomerId)
    Select OrderID, OrderDate, CustomerId From Orders
    Where OrderID in (Select OrderID from NewOrders);

Say the query is run for the first time and Orders contains 400 million rows or more:

Can SQL Server handle this number of rows in one single INSERT statement?

If not, how should I proceed? Should I limit the number of rows fetched in the INSERT statement? How many rows can the engine handle in a single INSERT statement?

Answer 1

According to the documentation the only limitation on the number of rows stored per table is as below

Limited only by available resources

What local resources does this potentially include?

• Memory
• Space in the database data files to physically write the inserted data to
• Space in the database log files
• Space in TempDB (Data and log)

As for how much a single SELECT statement can extract, I cannot find any documentation pertaining to this directly, but I would assume it is safe to take this to be exactly the same as the above.

Answer 2

There does not appear to be a row limit except as it relates to resources. If your SQL Server truly have enough resources to insert half a billion rows then it will do it. In the past, I have been faced with such resource constraints, so I would add a WHERE clause that limited the resultset. Or I would run the INSERT recursively with a TOP on the select.

With NewOrders 
As
(   
    Select OrderID From Orders
    Except
    Select OrderID From FactOrders
)
Insert Into FactOrders(OrderID, OrderDate, CustomerId)
Select **TOP 1000000** OrderID, OrderDate, CustomerId 
  From Orders
 Where OrderID in (Select OrderID from NewOrders);