Temporary table record limit in Sql server

Question

Is there any limits for the record in the temporary table.. I have tried with 1.3 million records.. may be i have to deal with billions in the future as the application demands.. Is it possible? If i could know the limit of records.. i could try to split the records from source db and manage within the limit. Thanks in advance

Answer 1

Any table will run into the hard limit of 1000 maximum rows that can be inserted with a single T-SQL INSERT statement that uses a table value constructor.

The number of row value expressions in the INSERT statement exceeds
the maximum allowed number of 1000 row values. 

https://dba.stackexchange.com/questions/82921/the-number-of-row-value-expressions-in-the-insert-statement-exceeds-the-maximum

Answer 2

A temporary table is a table so this would apply: Maximum Capacity Specifications for SQL Server

Rows per table

Limited by available storage

Answer 3

The differences between tempdb and any other database are minimal, especially when it comes to limits.

If you can store it in a user table, you can store it in a temporary table as well. It does not have to fit into RAM as the tempdb is stored on disk just like any other database (only with more aggressive caching and less logging).

Source: http://msdn.microsoft.com/en-us/library/ms190768.aspx