How to create Temp table with SELECT * INTO tempTable FROM CTE Query
Sample DDL
create table #Temp
(
EventID int,
EventTitle Varchar(50),
EventStartDate DateTime,
EventEndDate DatetIme,
EventEnumDays int,
EventStartTime Datetime,
EventEndTime DateTime,
EventRecurring Bit,
EventType int
)
;WITH Calendar
AS (SELECT /*...*/)
Insert Into #Temp
Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
,EventType from Calendar
where (PlannedDate >= GETDATE()) AND ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
or EventEnumDays is null
Make sure that the table is deleted after use
If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
Drop Table #Temp
End
How to create temp table using Create statement in SQL Server?
A temporary table can have 3 kinds, the #
is the most used. This is a temp table that only exists in the current session.
An equivalent of this is @
, a declared table variable. This has a little less "functions" (like indexes etc) and is also only used for the current session.
The ##
is one that is the same as the #
, however, the scope is wider, so you can use it within the same session, within other stored procedures.
You can create a temp table in various ways:
declare @table table (id int)
create table #table (id int)
create table ##table (id int)
select * into #table from xyz
SQL Server Creating a temp table for this query
If you want to just create a temp table inside the query that will allow you to do something with the results that you deposit into it you can do something like the following:
DECLARE @T1 TABLE (
Item 1 VARCHAR(200)
, Item 2 VARCHAR(200)
, ...
, Item n VARCHAR(500)
)
On the top of your query and then do an
INSERT INTO @T1
SELECT
FROM
(...)
How to create temp table with dynamic SQL query result
You can do:
SELECT ID
, AccountTypeName AS Type
, FirstName AS Name
, ' + @colsSelect + '
INTO ##TEMPTABLE
FROM cte AS t
Since you execute this dynamically, you cannot use #TEMPTABLE
because a local temp table will only exist in the scope of the query that defines it. Using ##
creates a global temp table which will be accessible outside the scope of the dynamic query.
How SQL query result insert in temp table?
Look at SELECT INTO. This will create a new table for you, which can be temporary if you want by prefixing the table name with a pound sign (#).
For example, you can do:
SELECT *
INTO #YourTempTable
FROM YourReportQuery
Insert Data Into Temp Table with Query
SELECT *
INTO #Temp
FROM
(SELECT
Received,
Total,
Answer,
(CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) AS application
FROM
FirstTable
WHERE
Recieved = 1 AND
application = 'MORESTUFF'
GROUP BY
CASE WHEN application LIKE '%STUFF%' THEN 'MORESTUFF' END) data
WHERE
application LIKE
isNull(
'%MORESTUFF%',
'%')
SQL Server declare and execute query for temp table
Try hash table as below:
DECLARE @tblTest AS Table
(
Name VARCHAR(50)
)
insert into @tblTest values('Sandip')
insert into @tblTest values('AAA')
IF OBJECT_ID('tempdb..#tblTest') IS NOT NULL
DROP TABLE #tblTest
SELECT * INTO #tblTest FROM @tblTest
DECLARE @query AS NVARCHAR(100) = 'SELECT * FROM #tblTest'
EXEC (@query)
Output:
Related Topics
How to Remove The Default Value from a Column in Oracle
How to Count Unique Pairs of Values in Sql
MySQL Count Data for Last 7 Days
The Identifier That Starts with ...... Is Too Long. Maximum Length Is 128
Sql Select Rows Containing Part of String
Replacing Variable Length String with Some Word
Sqlite: Alias Column Name Can't Contains a Dot "."
Does SQL Server Support Is Distinct from Clause
Join Table Twice - on Two Different Columns of The Same Table
Best Way to Find SQL Locks in SQL Server 2008
Can You Use Auto-Increment in MySQL with Out It Being The Primary Key
Sql Server Get The Full Month Name from a Date
Default Values for Columns in Big Query Tables