CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012temp-tables
user3115933
user3115933

Reputation: 4443

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

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

  1. Create a temporary table (let's call it #temptable).

  2. Insert values from tables located in an existing database into #temptable using a SELECT statement.

  3. 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?

Upvotes: 0

Views: 6277

Answers (1)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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.

Upvotes: 1

Related Questions