Insert Select data into Temporary Table in SQL 2012

Question

Question: How to Insert Complicated select data Into temp Table in sql 2012

select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type

I have Tried following solution

select * into #temptable from
(select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type)

I got following Error

Incorrect syntax near ')'.

When I am Normally running select statement i am getting expected data

• SQL_M: Created_dt, (comma should not be here)
• ravishankar chavare: in real query there is no comma i have edited check again
• Damien_The_Unbeliever: So, this is "fix my code, but I'm not going to show you my code"?
• ravishankar chavare: @Damien_The_unbeliever why
• Damien_The_Unbeliever: Well, so far someone's found one issue with the code, but it's with the "fake query" in the question, not the "real query" which you have actual problems with. There's an obvious extra ) at the end of your second query that isn't present in the first, but who knows whether that's relevant to your actual problem...

Answer 1

The following code is correct as far as syntax goes:

SELECT *
INTO #Temptable
FROM
(
 SELECT ROW_NUMBER() OVER(ORDER BY Ppt.Type) AS Item_Code,
 Ppt.Type AS Type,
 '11/02/19 09:51' AS Created_Dt
 FROM Product AS Psi
 INNER JOIN Db1..Items AS Ios ON Ios.Icode = Psi.Icode
 INNER JOIN Db2..Types AS Ppt ON Ppt.Type = Ios.Type );

Normally, you could capture the logic in a CTE and insert the CTE in your temp table.

USE SomeDB;
WITH CTE AS 
(
 SELECT *
 FROM
 (
 SELECT ROW_NUMBER() OVER(ORDER BY Ppt.Type) AS Item_Code,
 Ppt.Type AS Type,
 '11/02/19 09:51' AS Created_Dt
 FROM Product AS Psi
 INNER JOIN Db1..Items AS Ios ON Ios.Icode = Psi.Icode
 INNER JOIN Db2..Types AS Ppt ON Ppt.Type = Ios.Type )
)
INSERT INTO #T
SELECT * FROM CTE

• ravishankar chavare: Thanks its worked for me using common table expression

Answer 2

The problem is, you are inserting data from an unnamed data source to a table.

select * into #temptable from
(select ROW_NUMBER() OVER(order by ppt.type) as Item_code,
ppt.type type,
...,
...,
...,
'11/02/19 09:51' Created_dt
from product psi
inner join [DB1]..items ios on ios.icode=psi.icode
inner join [DB2]..types ppt on ppt.type=ios.type) as tbl

Just go with this and your problem will be solved. I have just added a alias for the source you are inserting data.