Create Temp table in MSSQL

In Microsoft SQL Server (MSSQL), you can create a temporary table using the CREATE TABLE statement with the # prefix for a local temporary table or the ## prefix for a global temporary table. Local temporary tables are visible only to the current session, while global temporary tables are visible to all sessions and are dropped when the creating session ends.

Here's how you can create a local temporary table:

CREATE TABLE #TempTable (

    ID INT,

    Name VARCHAR(50)

);

This statement creates a temporary table named #TempTable with columns ID and Name. This table is only visible within the current session and will be automatically dropped when the session ends.

If you want to create a global temporary table instead:

CREATE TABLE ##GlobalTempTable (

    ID INT,

    Name VARCHAR(50)

);

This statement creates a global temporary table named ##GlobalTempTable. It will be visible to all sessions, and it will be dropped when the last session referencing it ends.

You can then use these temporary tables in your SQL queries within the same session or across sessions if it's a global temporary table. Make sure to drop the temporary tables when they are no longer needed to free up resources:

DROP TABLE #TempTable;

DROP TABLE ##GlobalTempTable;

Remember, local temporary tables are automatically dropped when the session ends, so you don't need to explicitly drop them.