Insert into temp table using a select

Christopher Jack 1,616 Reputation points
2021-02-24T11:55:32.883+00:00

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?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,231 Reputation points
    2021-02-24T12:05:53.037+00:00

    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
    

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 43,246 Reputation points
    2021-02-24T12:06:43.35+00:00

    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)
    
    1 person found this answer helpful.