Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
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
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)