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.
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
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
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
