How to Create a Temporary Table from a Type

How do I create a temporary table from a type?

Pure.Krome's answer shows how to use a table variable instead of a #temp table. If you really want a #temp table based on a known table type (without having to know the column names/definitions), you can say:

DECLARE @d usr.NameList;
SELECT * INTO #superBrand FROM @d;

Now, #superBrand should match the table structure of the table type, minus the constraints (and marginally useful secondary indexes, starting with SQL Server 2014).

Of course, the rest of your code that then populates the #temp table is going to have to know the structure. So, what exactly is the purpose of declaring a #temp table with the same structure as a table type?

Create a temporary table in a SELECT statement without a separate CREATE TABLE

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Best way to create a temp table with same columns and type as a permanent table

select top 0 *
into #mytemptable
from myrealtable

How to create temporary table with given column names from select?

as newData works just as you'd expect it to;

mysql> CREATE TABLE myTable (
  id INT NOT NULL DEFAULT 0,
  data INT
);

mysql> CREATE TEMPORARY TABLE tmp AS
         SELECT id, min(data) as newData FROM myTable WHERE id > 100 GROUP BY id;

mysql> desc tmp;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(11) | NO   |     | 0       |       |
| newData | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

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

Create a temporary table dynamically from calling an stored procedure

My ultimate goal is to compare columns data type of a 2nd resultset
from an SP with my expected table schema using tSQLt test case.

Refactoring the code returning a second resultset into its own proc would make this more easy to test but it is do-able.

Supposing your procedure under test looks like

CREATE PROCEDURE dbo.ProcedureUnderTest
AS
BEGIN

SELECT 1 AS ResultSet1Col1

SELECT 2 AS ResultSet2Col1, 'Foo' AS ResultSet2Col2

END

You can achieve your desired goal of validating the format of the second result set by nesting a call to tSQLt.ResultSetFilter inside an execution of tSQLt.AssertResultSetsHaveSameMetaData

CREATE TABLE #expected
(
   ResultSet2Col1 INT NULL,
   ResultSet2Col2 VARCHAR(3) NULL
)

EXEC tSQLt.AssertResultSetsHaveSameMetaData
  @expectedCommand = 'SELECT * FROM #expected',
  @actualCommand = 'EXEC tSQLt.ResultSetFilter 2, ''EXEC dbo.ProcedureUnderTest'';' 

select into temporary table

There are few different approaches:

1. Use the immediate arg in your DBI::dbExecute statement

DBI::dbExecute(conn, "select top 5 * into #local from sometable", immediate=TRUE)
DBI::dbGetQuery(conn, "select * from #local")

2. Use a global temp table

DBI::dbExecute(conn, "select top 5 * into ##global from sometable")
DBI::dbGetQuery(conn, "select * from ##global")

3. Use dplyr/dbplyr

tt = tbl(conn, sql("select top 5 * from sometable")) %>% compute()
tt

Also see here: https://github.com/r-dbi/odbc/issues/127

---

Related Topics