Is it possible to create indexes on a temp table when using SELECT INTO?
indexing, select-into, sql, t-sql
Solution
The table created by `SELECT INTO` is always a heap. If you want a PK/Identity column you can either do as you suggest in the comments
CREATE TABLE #T
(
Id INT IDENTITY(1,1) PRIMARY KEY,
/*Other Columns*/
)
INSERT INTO #T
SELECT *
FROM TradeTable.staging.Security
Or avoid the explicit `CREATE` and need to list all columns out with
SELECT TOP (0) IDENTITY(int,1,1) As Id, *
INTO #T
FROM TradeTable.staging.Security
ALTER TABLE #T ADD PRIMARY KEY(Id)
INSERT INTO #T
SELECT *
FROM TradeTable.staging.Security
Problem
I am loading data from a CSV file into a temp staging table and this temp table is being queried a lot. I looked at my execution plan and saw that a lot of the time is spent scanning the temp table. Is there any way to create index on this table when I `SELECT INTO` it? ``` SELECT * FROM TradeTable.staging.Security s WHERE ( s.Identifier IS NOT NULL OR s.ConstituentTicker IS NOT NULL OR s.CompositeTicker IS NOT NULL OR s.CUSIP IS NOT NULL OR s.ISIN IS NOT NULL OR s.SEDOL IS NOT NULL OR s.eSignalTicker IS NOT NULL) ```