Is it possible to create indexes on a temp table when using SELECT INTO?

Question

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)

enter image description here

@HamletHakobyan you cannot create a table and use select into to insert into it. — HLGEM, Dec 21 '12 at 20:30
@HamletHakobyan It's a heap table - so I would need to add a column for PK then? — Ian R. O'Brien, Dec 21 '12 at 20:31
Have you tried running a create index statement as the next statement in the batch? — HLGEM, Dec 21 '12 at 20:31
@HLGEM It looks like the way I will solve this is 1) Create the temp table with `CREATE TABLE` and PK/Identity column and then 2) perform `INSERT` for all data. I can add indexes between 1) and 2). — Ian R. O'Brien, Dec 21 '12 at 20:41
Yes insert is for existing table (permanent or temp) so you can create index. Select into is creates the table with no indexes. — paparazzo, Dec 21 '12 at 21:06
I know it wasn’t specified, but if you are creating a clustered index, the best route is to use `CREATE TABLE #t` (even though it requires repeating one’s self) because the `INSERT INTO #t SELECT` will [insert the data in the right order in the most efficient way](https://stackoverflow.com/a/1029695/429091). Creating the index after the fact will cause more work. — binki, Jan 11 '18 at 15:18

score 30 · Accepted Answer · answered Dec 21 '12 at 21:08

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

Is it possible to create indexes on a temp table when using SELECT INTO?

1 Answers1