In the User-Defined Temp Table window, if the commands for creating the temporary table contain parameters, you are prompted to enter the data types and values on the Parameters window after you click Execute.
After the user-defined temporary tables are created, you can view the SQL scripts used to create and populate the tables.
To view a script
Click .
Select the Temp Table List tab.
Select the temporary table from the Temp Table drop down list.
After the User-Defined temporary tables are created, you can drop them.
To drop temporary tables
Click .
Select the Temp Table List tab.
If you want to remove all temp tables, click Drop All Table.
Example with CREATE TABLE in the source code containing the following SQL statements:
/* SQL1 */
CREATE TABLE #temp1
([EMP_ID] float NOT NULL,
[EMP_NAME] varchar (40) NOT NULL)
/* SQL2 */
Insert into #temp1 (EMP_ID, EMP_NAME)
values (1, 'Rene')
/* SQL3 */
Insert into #temp1 (EMP_ID, EMP_NAME)
values (2, 'Andrew')
/* SQL4 */
Insert into #temp1 (EMP_ID, EMP_NAME)
values (3, 'Claudia')
/* SQL5 */
Select * from #temp1
In the above example, when the SQL Scanner finds the first INSERT statement, it executes the CREATE TABLE statement, so that the temporary table #temp1 is created. The query plan for the INSERT statement is obtained and the statement is classified as to how likely it is to be causing a performance problem. In the SQL Information Pane of the SQL Scanner window, the Scanner Temp Table pane displays the SQL statements used to create the temporary table. At then end of scanning process, the temporary table is dropped unless you have set the Override User-Defined Temp Table option.
In the Preferences, an option is provided to include the data in the temporary table. When you select the Include data option, the INSERT, UPDATE and DELETE statements that populate the temporary table are also executed. These statements are displayed along with the CREATE TABLE command in the Information Pane in the SQL Scanner window.
From the example above, the SQL Scanner window shows the following for SQL5 (select * from #temp1) under the Scanner Temp Table button. It shows the SQL statement used to create the temporary table and the data that is inserted into it during the scanning process.
/* with Included data option selected in Preferences */
CREATE TABLE #temp1
([EMP_ID] float NOT NULL,
[EMP_NAME] varchar (40) NOT NULL)
Insert into #temp1 (EMP_ID, EMP_NAME)
values (1,'Rene')
Insert into #temp1 (EMP_ID, EMP_NAME)
values (2,'Andrew')
Insert into #temp1 (EMP_ID, EMP_NAME)
values (3,'Claudia')
Example using SELECT INTO to create the temporary table.
After scanning, the SQL Scanner window shows the following two statements:
/* SQL1 */
select EMP_ID,
EMP_NAME
into #temp1
from EMPLOYEE
/* SQL2 */
select *
from #temp1
For SQL2, the Scanner Temp Table pane shows that the SELECT INTO SQL statement was used to create the temporary table.
select EMP_ID,
EMP_NAME
into #temp1
from EMPLOYEE
© ALL RIGHTS RESERVED. Nutzungsbedingungen Datenschutz Cookie Preference Center