INSERT INTO temp table with subquery

Question

I am trying to insert into temp table with a insert into....select. my current statement looks like the following:

insert into ods.account
(
  AccountNumber,
  AccountName,
  AccountCreated,
  AccountEnded,
  PayoffDate
)
select
  AccountNumber
  , AccountName
  , AccountCreated
  , AccountEnded
  , PayoffDate 
from dbo.InsuranceAccount

I want to be able to put this information into a temp table if possible I would like to insert into temp table at the top of my query in SQL Server.

Possible duplicate of [How to create Temp table with SELECT \* INTO tempTable FROM CTE Query](https://stackoverflow.com/questions/11491240/how-to-create-temp-table-with-select-into-temptable-from-cte-query) — Jerome, Jul 10 '19 at 03:14

haku · Answer 1 · 2019-07-10T03:26:48.183

You could simply do:

select
  AccountNumber
  , AccountName
  , AccountCreated
  , AccountEnded
  , PayoffDate into #myTempTable
from dbo.InsuranceAccount

or you could create your temp table first and then do the insert:

create table #tempTable(AccountName varchar(100), AccountNumber int)-- so on..
insert into #tempTable(AccountName, AccountNumber)
select AccountName, AccountNumber from dbo.InsuranceAccount

score 0 · Answer 2 · answered Jul 10 '19 at 03:30

There are a couple ways you can do this using temp tables in SQL.

If you simply just want to insert the contents of a select into a temp table, use the following method. Keep in mind, you cannot use this in a loop because it creates the temp table each time the select runs:

select
  AccountNumber
  , AccountName
  , AccountCreated
  , AccountEnded
  , PayoffDate 
into #temp_table      
from dbo.InsuranceAccount

You can also pre-define the temp table and then use INSERT INTO as you would any other table. This method is good because you can use this kind of temp table in a WHILE loop and insert data each loop through.

create table #temp_table
(
  field1 int not null,
  field2 char(1),
  ...
)

insert into #temp_table
select 
  AccountNumber
  , AccountName
  , AccountCreated
  , AccountEnded
  , PayoffDate      
from dbo.InsuranceAccount

score 0 · Answer 3 · answered Jul 10 '19 at 03:33

This may help.

IF OBJECT_ID('temp..#InsuranceAccount') IS NOT NULL
BEGIN
    DROP TABLE #InsuranceAccount
END

SELECT  AccountNumber
      , AccountName
      , AccountCreated
      , AccountEnded
      , PayoffDate 
INTO  #InsuranceAccount
FROM dbo.InsuranceAccount

INSERT INTO temp table with subquery

3 Answers3