Insert into temp table using a select

Question

Hi,

I am trying to insert into a temp table using

select * into #join from
 SELECT 
 0 as 'OrgSalesOrder',
 p.OrderNumber 'SalesOrder',
 p.InvoiceNumber,
 p.CreditNumber,
 p.PaymentID as 'PaymentID',
 p.Currency as 'Currency',
 --c.CustomerID,
 --concat(c.FirstName + ' ', c.LastName) as CustomerName,
 d.FullDate,
 pc.PaymentName as PaymentMethod,
 ps.[Description] as PaymentStatus,
 p.PaymentValue,
 cn.Authorisation as ChequeNum,
 tt.TransactionType,
 p.ManualRefund,
 case when
 p.PaymentID like 'D%' then so.SalesOfficeKey
 else
 p.SalesOfficeKey 
 end as SalesOffice 
 FROM
 #testPayments p
 left join #DreamPayments DP on p.PaymentID =  'D' + cast(dp.PaymentNumber as varchar) 
 left join test.DimSalesOffice so on DP.SalesOffice = so.SalesOfficeCode
 INNER JOIN dbo.DimDate d on p.PaymentDateKey = d.DateKey
 INNER JOIN [dbo].[DimPaymentStatus] ps on p.PaymentStatusKey = ps.PaymentStatusKey
 INNER JOIN [dbo].[DimTransactionType] tt on p.TransactionTypeKey = tt.TransactionTypeKey
 Left outer join [dbo].[DimPaymentCode] pc on p.PaymentCodeKey = pc.PaymentCodeKey
 LEFT OUTER JOIN #ChequeNum cn ON p.PaymentID = Cast(cn.Payment_Num as Nvarchar)

I am getting the following error

Incorrect syntax near the keyword 'SELECT'.

Is it possible to use a select query to insert into a temp table?

Answer 1

One way is to define the source query as a derived table (wrapped in parenthesis with an alias). Below is an example:

select * into #join
from (
 SELECT 
 0 as 'OrgSalesOrder',
 p.OrderNumber 'SalesOrder',
 p.InvoiceNumber,
 p.CreditNumber,
 p.PaymentID as 'PaymentID',
 p.Currency as 'Currency',
 --c.CustomerID,
 --concat(c.FirstName + ' ', c.LastName) as CustomerName,
 d.FullDate,
 pc.PaymentName as PaymentMethod,
 ps.[Description] as PaymentStatus,
 p.PaymentValue,
 cn.Authorisation as ChequeNum,
 tt.TransactionType,
 p.ManualRefund,
 case when
 p.PaymentID like 'D%' then so.SalesOfficeKey
 else
 p.SalesOfficeKey 
 end as SalesOffice 
 FROM
 #testPayments p
 left join #DreamPayments DP on p.PaymentID =  'D' + cast(dp.PaymentNumber as varchar) 
 left join test.DimSalesOffice so on DP.SalesOffice = so.SalesOfficeCode
 INNER JOIN dbo.DimDate d on p.PaymentDateKey = d.DateKey
 INNER JOIN [dbo].[DimPaymentStatus] ps on p.PaymentStatusKey = ps.PaymentStatusKey
 INNER JOIN [dbo].[DimTransactionType] tt on p.TransactionTypeKey = tt.TransactionTypeKey
 Left outer join [dbo].[DimPaymentCode] pc on p.PaymentCodeKey = pc.PaymentCodeKey
 LEFT OUTER JOIN #ChequeNum cn ON p.PaymentID = Cast(cn.Payment_Num as Nvarchar)
 ) AS derived_table

Answer 2

Do you want to create the temp table with the SELECT result? Then remove first line of code and place the INTO clause in front of the other FROM clause:

  SELECT 
  0 as 'OrgSalesOrder',
  p.OrderNumber 'SalesOrder',
  p.InvoiceNumber,
  p.CreditNumber,
  p.PaymentID as 'PaymentID',
  p.Currency as 'Currency',
  --c.CustomerID,
  --concat(c.FirstName + ' ', c.LastName) as CustomerName,
  d.FullDate,
  pc.PaymentName as PaymentMethod,
  ps.[Description] as PaymentStatus,
  p.PaymentValue,
  cn.Authorisation as ChequeNum,
  tt.TransactionType,
  p.ManualRefund,
  case when
  p.PaymentID like 'D%' then so.SalesOfficeKey
  else
  p.SalesOfficeKey 
  end as SalesOffice 

into #join

  FROM
  #testPayments p
  left join #DreamPayments DP on p.PaymentID =  'D' + cast(dp.PaymentNumber as varchar) 
  left join test.DimSalesOffice so on DP.SalesOffice = so.SalesOfficeCode
  INNER JOIN dbo.DimDate d on p.PaymentDateKey = d.DateKey
  INNER JOIN [dbo].[DimPaymentStatus] ps on p.PaymentStatusKey = ps.PaymentStatusKey
  INNER JOIN [dbo].[DimTransactionType] tt on p.TransactionTypeKey = tt.TransactionTypeKey
  Left outer join [dbo].[DimPaymentCode] pc on p.PaymentCodeKey = pc.PaymentCodeKey
  LEFT OUTER JOIN #ChequeNum cn ON p.PaymentID = Cast(cn.Payment_Num as Nvarchar)