CODEWITHSUNDEEP
sqlsql-server
ravishankar chavare
ravishankar chavare

Reputation: 503

Insert Select data into Temporary Table in SQL 2012

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

Upvotes: 0

Views: 1379

Answers (2)

Rajan Mishra
Rajan Mishra

Reputation: 1178

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.

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

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

Upvotes: 2

Related Questions