How to insert the output of a SQL Query into a temporary table?

Question

I am using SQL Server 2012 and I want to perform the following operations using T-SQL:

Create a temporary table (let's call it #temptable).
Insert values from tables located in an existing database into #temptable using a SELECT statement.
Write a SELECT statement that will query the #temptable.

I have tried the following but it seems the syntax is not correct or perhaps what I am trying to do is not allowed.

USE MyDatabase

CREATE TABLE #temptable
GO

INSERT INTO #TempTable
SELECT  [Col A], [Col B], [Col C]

FROM MYLIST

WHERE [MONTH OF STAY] = '2018-03-01'

AND [Property] = 'ABC'

SELECT * FROM #temptable

How do I move forward with this?

Vahid Farahmandian · Accepted Answer · 2018-01-27 10:04:06Z

1

In create temp table, you need to specify the columns and datatypes too. Temp table is created just like a persistant table. However you can use Select Into statement, which will create the table automatically based on the given Select statement Try This:

USE MyDatabase
GO

IF OBJECT_ID('tempdb..#TempTable') Is Not null
    Drop Table #TempTable

SELECT  [Col A], [Col B], [Col C]
INTO #TempTable -- <<<<<
FROM MYLIST

WHERE [MONTH OF STAY] = '2018-03-01'

AND [Property] = 'ABC'

SELECT * FROM #temptable

In above code, you need to check if table with the given name is exists or not? If exists, then you will need to drop it, prior to execute the Select Into. Because Select Into will automatically create the table for you, and if table with same name exists, then you will get error.

edited Jan 27, 2018 at 10:04

answered Jan 27, 2018 at 9:58

Vahid Farahmandian

6,6227 gold badges48 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert the output of a SQL Query into a temporary table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related