- CATALOG -
Creating a Second Temporary Table in SQL: A Step-by-Step Guide to Creating, Dropping and Using Multiple Temporary Tables in T-SQL
folder [sql]  

Creating a 2nd Temporary Table in SQL: A Step-by-Step Guide

Temporary tables, also known as derived tables or inline views, are used to store data that is needed only for a specific period of time. They can be created using the CREATE TABLE statement with the TEMPORARY keyword or by using the WITH clause (Common Table Expressions) in SQL Server.

In this article, we will explore how to create a 2nd temporary table in SQL and explain the process in detail.

Understanding Temporary Tables

Temporary tables are stored in memory, which means they are deleted automatically when the session is closed. This makes them ideal for use cases where data is only needed temporarily.

Temporary tables can be created using several methods:

  • CREATE TABLE: The CREATE TABLE statement with the TEMPORARY keyword can be used to create a temporary table.
  • WITH: The WITH clause (Common Table Expressions) can also be used to create temporary tables. These are often referred to as derived tables or inline views.

Creating Temporary Tables Using the CREATE TABLE Statement

The CREATE TABLE statement is one of the most common methods used to create temporary tables in SQL Server.

Here’s an example:

-- Create a temporary table using the CREATE TABLE statement
CREATE TABLE #temp_main (
    PDITM# INT,
    SGDWHS CHAR(6),
    PDCHN CHAR(3),
    PWDEDT DATE,
    Sum_PDQSLD DECIMAL(18,2),
    DHSLD DECIMAL(18,2),
    WMSLD DECIMAL(18,2),
    MJSLD DECIMAL(18,2),
    BJSLD DECIMAL(18,2)
);

-- Insert data into the temporary table
INSERT INTO #temp_main (PDITM#, SGDWHS, PDCHN, PWDEDT, Sum_PDQSLD, DHSLD, WMSLD, MJSLD, BJSLD)
SELECT 
    p.PDITM#,
    l.SGDWHS,
    p.PDCHN,
    p.PDWEDT,
    SUM(p.PDQSLD) AS [Sum_PDQSLD],
    CASE WHEN p.PDCHN = 'DH' THEN p.PDQSLD ELSE 0 END AS [DHSLD],
    CASE WHEN p.PDCHN = 'WM' THEN p.PDQSLD ELSE 0 END AS [WMSLD],
    CASE WHEN p.PDCHN = 'MJ' THEN p.PDQSLD ELSE 0 END AS [MJSLD],
    CASE WHEN p.PDCHN = 'BJ' THEN p.PDQSLD ELSE 0 END AS [BJSLD]
FROM 
    [Repit].[LEVYDTA].[POSDTLM] AS p
LEFT JOIN 
    [Repit].[LEVYDTA].[LDSSGEN] AS l ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
WHERE 
    p.PDQSLD > 0 AND p.[PDAGY] != 15 AND l.[SGDWHS] IN (01, 21, 30, 40, 02)
    AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT)) = CAST(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) AS date)
GROUP BY 
    p.[PDITM#],
    l.[SGDWHS],
    p.[PDCHN],
    p.[PWDEDT],
    p.[PDQSLD]
);

Creating Temporary Tables Using the WITH Clause

Another method of creating temporary tables is by using the WITH clause, also known as Common Table Expressions (CTEs).

Here’s an example:

-- Create a temporary table using the WITH clause
WITH t AS (
    SELECT 
        p.PDITM#,
        l.SGDWHS,
        p.PDCHN,
        p.PDWEDT,
        SUM(p.PDQSLD) AS [Sum_PDQSLD],
        CASE WHEN p.PDCHN = 'DH' THEN p.PDQSLD ELSE 0 END AS [DHSLD],
        CASE WHEN p.PDCHN = 'WM' THEN p.PDQSLD ELSE 0 END AS [WMSLD],
        CASE WHEN p.PDCHN = 'MJ' THEN p.PDQSLD ELSE 0 END AS [MJSLD],
        CASE WHEN p.PDCHN = 'BJ' THEN p.PDQSLD ELSE 0 END AS [BJSLD]
    FROM 
        [Repit].[LEVYDTA].[POSDTLM] AS p
    LEFT JOIN 
        [Repit].[LEVYDTA].[LDSSGEN] AS l ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
    WHERE 
        p.PDQSLD > 0 AND p.[PDAGY] != 15 AND l.[SGDWHS] IN (01, 21, 30, 40, 02)
        AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT)) = CAST(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) AS date)
    GROUP BY 
        p.[PDITM#],
        l.[SGDWHS],
        p.[PDCHN],
        p.[PWDEDT],
        p.[PDQSLD]
),
t2 AS (
    SELECT 
        t.PDITM#,
        t.SGDWHS,
        SUM(t.DHSLD) AS [DHSLD],
        SUM(t.WMSLD) AS [WMSLD],
        SUM(t.MJSLD) AS [MJSLD],
        SUM(t.BJSLD) AS [BJSLD]
    FROM 
        t
    GROUP BY 
        t.PDITM#,
        t.SGDWHS
)
SELECT 
    t2.PDITM#,
    t2.SGDWHS,
    CAST((t2.DHSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [DHPCT],
    CAST((t2.WMSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [WMPCT],
    CAST((t2.MJSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [MJPCT],
    CAST((t2.BJSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [BJPCT],
    CAST(((t2.Sum_PDQSLD - (t2.DHSLD + t2.WMSLD + t2.MJSLD + t2.BJSLD))/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [OTHRPCT]
FROM 
    t2
ORDER BY 
    t2.PDITM#,
    t2.SGDWHS;

Creating a Second Temporary Table

To create a second temporary table, you can use the CREATE TABLE statement with the TEMPORARY keyword, or by using the WITH clause.

Here’s an example:

-- Create a second temporary table
-- Using the CREATE TABLE statement
CREATE TABLE #temp_main_2 (
    PDITM# INT,
    SGDWHS CHAR(6),
    PDCHN CHAR(3),
    PWDEDT DATE,
    Sum_PDQSLD DECIMAL(18,2),
    DHSLD DECIMAL(18,2),
    WMSLD DECIMAL(18,2),
    MJSLD DECIMAL(18,2),
    BJSLD DECIMAL(18,2)
);

-- Insert data into the second temporary table
INSERT INTO #temp_main_2 (PDITM#, SGDWHS, PDCHN, PWDEDT, Sum_PDQSLD, DHSLD, WMSLD, MJSLD, BJSLD)
SELECT 
    t.PDITM#,
    t.SGDWHS,
    p.PDCHN,
    p.PDWEDT,
    SUM(t.Sum_PDQSLD) AS [Sum_PDQSLD],
    CASE WHEN p.PDCHN = 'DH' THEN t.DHSLD ELSE 0 END AS [DHSLD],
    CASE WHEN p.PDCHN = 'WM' THEN t.WMSLD ELSE 0 END AS [WMSLD],
    CASE WHEN p.PDCHN = 'MJ' THEN t.MJSLD ELSE 0 END AS [MJSLD],
    CASE WHEN p.PDCHN = 'BJ' THEN t.BJSLD ELSE 0 END AS [BJSLD]
FROM 
    t
JOIN 
    p ON p.PDITM# = p.[PDAGY] AND p.PDCHN = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
WHERE 
    t.Sum_PDQSLD > 0 AND t.DHSLD > 0 AND t.WMSLD > 0
GROUP BY 
    t.PDITM#,
    t.SGDWHS,
    p.PDCHN,
    p.PDWEDT,
    t.Sum_PDQSLD;

Creating a Second Temporary Table Using the WITH Clause

You can also create a second temporary table using the WITH clause.

-- Create a second temporary table using the WITH clause
WITH 
    t AS (
        SELECT 
            p.PDITM#,
            l.SGDWHS,
            p.PDCHN,
            p.PDWEDT,
            SUM(p.PDQSLD) AS [Sum_PDQSLD],
            CASE WHEN p.PDCHN = 'DH' THEN p.PDQSLD ELSE 0 END AS [DHSLD],
            CASE WHEN p.PDCHN = 'WM' THEN p.PDQSLD ELSE 0 END AS [WMSLD],
            CASE WHEN p.PDCHN = 'MJ' THEN p.PDQSLD ELSE 0 END AS [MJSLD],
            CASE WHEN p.PDCHN = 'BJ' THEN p.PDQSLD ELSE 0 END AS [BJSLD]
        FROM 
            [Repit].[LEVYDTA].[POSDTLM] AS p
        LEFT JOIN 
            [Repit].[LEVYDTA].[LDSSGEN] AS l ON p.[PDAGY]=l.[SGAGY] AND p.[PDCHN] = l.[SGCHN] AND p.[PDSTR] = l.[SGSTR]
        WHERE 
            p.PDQSLD > 0 AND p.[PDAGY] != 15 AND l.[SGDWHS] IN (01, 21, 30, 40, 02)
            AND CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT)) = CAST(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) AS date)
        GROUP BY 
            p.[PDITM#],
            l.[SGDWHS],
            p.[PDCHN],
            p.[PWDEDT],
            p.[PDQSLD]
    ),
    t2 AS (
        SELECT 
            t.PDITM#,
            t.SGDWHS,
            SUM(t.DHSLD) AS [DHSLD],
            SUM(t.WMSLD) AS [WMSLD],
            SUM(t.MJSLD) AS [MJSLD],
            SUM(t.BJSLD) AS [BJSLD]
        FROM 
            t
        GROUP BY 
            t.PDITM#,
            t.SGDWHS
    )
SELECT 
    t2.PDITM#,
    t2.SGDWHS,
    CAST((t2.DHSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [DHPCT],
    CAST((t2.WMSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [WMPCT],
    CAST((t2.MJSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [MJPCT],
    CAST((t2.BJSLD/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [BJPCT],
    CAST(((t2.Sum_PDQSLD - (t2.DHSLD + t2.WMSLD + t2.MJSLD + t2.BJSLD))/t2.Sum_PDQSLD) * 100 AS DECIMAL(18,2)) AS [OTHRPCT]
FROM 
    t2
ORDER BY 
    t2.PDITM#,
    t2.SGDWHS;

Dropping Temporary Tables

Temporary tables are automatically dropped when the session is closed. However, you can also drop them manually using the DROP TABLE statement.

Here’s an example:

-- Drop a temporary table
DROP TABLE #temp_main;

Note that the # symbol is used to indicate that the table is a temporary table.

Conclusion

Temporary tables are a useful feature in SQL Server, allowing you to store data that is needed only for a specific period of time. They can be created using the CREATE TABLE statement with the TEMPORARY keyword or by using the WITH clause (Common Table Expressions). Creating multiple temporary tables requires similar steps and syntax as creating one temporary table.

Last modified on 2024-12-05

Real-World Coding Tutorials
Hugo Theme Diary by Rise
Ported from Makito's Journal.

© 2025 Real-World Coding Tutorials
- CATALOG -