How many records can insert into a temp table?

  • I use code below to insert data into a temp table.

    How many records can insert into a temp table?

    Select * into #temp from ORDER

  • I don't know of a limit to the number of rows, so I guess it would depend on your available server memory. One thing you should do is watch your use of reserved words as object names. In your query, you should quote your table name like this:

    Select * into #temp from [ORDER];

    I don't think it'll work without the name quoted because it's a reserved word.

  • Rows will be limited by storage and that's storage in tempdb. Here's a listing of all the limits on SQL Server. Books Online is always one of the single best resources.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The maximum size of TempDB, like all other databases, is ~525 000 TB, so that's the limit to how many rows you can put into a Temp Table. Probably a bit less, there's other stuff in TempDB as well.

    Not limited by available memory because neither TempDB nor temp tables (nor table variables for that matter) are memory-only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply