The SELECT INTO statement performs two functions: it first creates a table with the exact same structure as the table being selected from, and then it inserts all rows that meet the WHERE conditions (if a WHERE clause is used) of the table being selected from. The worktable created for the SELECT_INTO statement is slightly misleading, in that the worktable it refers to is actually the new physical table created. Unlike other worktables, it is not dropped when the query finishes executing. In addition, the worktable is not created in tempdb, unless the user specifies tempdb as the target database for the new table.
SELECT * INTO seattle_stores FROM stores WHERE city = 'Seattle'
STEP 1 The type of query is TABCREATE STEP 2 The type of query is INSERT The update mode is direct Worktable created for SELECT_INTO FROM TABLE stores Nested iteration Table Scan TO TABLE seattle_stores