Insert Data Into Temp Table with Query

sql

sql-server

temp-tables

conditional-statements

byAnton Shumikhin·Aug 25, 2024

Quickly populate a temporary table with the SELECT INTO command to create and fill it in one step. Or use INSERT INTO ... SELECT to feed an existing temp table. Here's the meat of it:

-- Create and populate new temp table:
SELECT * INTO #Temp FROM SourceTable WHERE Condition;

-- Insert into existing temp table:
INSERT INTO #Temp (Col1, Col2) SELECT Col1, Col2 FROM SourceTable WHERE Condition;

Update #Temp to your temporary table's name, SourceTable to your data source, and Condition to suit your filtering requirements.

The Devil's in the Details

Handling conditionals like a boss with ISNULL and CASE

Ensure data accuracy like a pro by using ISNULL to handle NULL values. Use CASE to manage conditional logic inside the SELECT statement because 'CASE' isn't just for detectives. 😉

-- Using ISNULL and CASE to play spy with data:
INSERT INTO #Temp (AppColumn, OtherColumn)
SELECT 
  CASE WHEN Application LIKE '%Critical%' THEN 'HighPriority' 
       ELSE 'Standard'
  END AS AppStatus, 
  ISNULL(Feature, 'N/A') AS FeatureStatus
FROM Applications
WHERE Application LIKE ISNULL('%MORESTUFF%', '%');

Syntax harmony and the underrated

Ensure column names in source and destination match, unless you like errors. 💥 Use # for temporary tables to avoid "oops, I dropped the wrong table" moments.

GROUP BY to the rescue for data grouping

Use GROUP BY for some killer data organization and analysis. It's like throwing a party and ensuring like-minded people stick together.

-- Party planning with SQL GROUP BY:
SELECT Department, COUNT(*) AS EmployeeCount
INTO #DepartmentSummary
FROM Employees
GROUP BY Department;

Preparing the work area

Use OBJECT_ID to annihilate an existing temp table before a new one rises from its ashes, like a phoenix. 🦅 Okay, maybe not that dramatic, but you get it.

-- Operation Phoenix:
IF OBJECT_ID('tempdb..#ExistingTemp') IS NOT NULL
  DROP TABLE #ExistingTemp;

SQL Server version is the new black

Specific SQL Server versions have specific requirements. If you're lucky enough to have SQL Server 2016 or later, table aliasing with AS makes your code look so sleek, it could walk a fashion runway.

-- Channeling SQL's inner fashionista:
SELECT * INTO #TempTable AS t FROM SourceTable WHERE Condition;

Dispose like a responsible coder

Being a good SQL citizen means dropping the temp table once you're done. It's like cleaning your room after a party, but less painful.

-- SQL's version of afterparty cleanup:
DROP TABLE #Temp;

Going Above and Beyond

Pivoting on a dime - into a temp table

Stowing pivot results in a temporary table simplifies complex reporting and analytics tasks, like turning a Rubik's cube into a solved puzzle. 🧩

-- Complex made easy with pivot:
SELECT *
INTO #TempPivot
FROM (
  SELECT Employee, Department, Salary
  FROM EmployeeSalaries
) AS SourceTable
PIVOT (
  SUM(Salary)
  FOR Department IN ([Sales], [IT], [HR])
) AS PivotTable;