How to insert into a temporary table specified in SQL Server trace

Question

CREATE TABLE #names ( [name] nvarchar(max) ); INSERT INTO #names ([name]) SELECT CustomerName from CustomerInformation Where status=3 

Whether INSERT INTO #names ... will be displayed in the SQL Server trace as INSERT for the table in tempdb or select from CustomerInformation. Or both appear in the trace?

Basically, will the trace be displayed as an insert or select?

Answer 1

Depends on what event you are really looking for in Trace:

• SQL: StmtCompleted and SP: StmtCompleted will show the statement that executed: INSERT INTO ... SELECT FROM ...
• SQL: BatchCompleted will display the full executable command (query) SQL: CREATE TABLE ...; INSERT INTO ... SELECT FROM ... CREATE TABLE ...; INSERT INTO ... SELECT FROM ...

Other events included in Trace will be displayed accordingly (locks, security checks, query plans, etc. etc.). But the essence of your question is this: INSERT INTO ... SELECT ... FROM ... is one one statement, not two statements.

Answer 2

Your expression will appear on the track once, just like you enter it. The database associated with the statement will be the current database when the command is executed. Since you do not specify the database in the FROM clause, the current database will be the database in which the CustomerInformation table is located.