CODEWITHSUNDEEP
sqlsql-server-2008
HEEN
HEEN

Reputation: 4721

Inserting data into temp table causing error

I want to insert a data into a temp table but it is not working for me and causing an error:

Invalid object name '#storedTemptable'.

Query is as below

INSERT INTO #storedTemptable --(Emp_mkey, data, cnt) 
    SELECT DISTINCT
        emp_mkey, data, COUNT(*) cnt 
    FROM 
        (SELECT * 
         FROM Emp_mon_day 
         WHERE emp_mkey IN (SELECT emp_card_no 
                            FROM emp_mst 
                            WHERE comp_mkey IN (7, 110)) 
           AND Year = 2016 
           AND month = 2) s
unpivot
(
    data for day in ([Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],
                [Day13],[Day14],[Day15],[Day16],[Day17],[Day18],[Day19],[Day20],[Day21],[Day22],[Day23],
                [Day24],[Day25],[Day26],[Day27],[Day28],[Day29],[Day30]) 
) up 
GROUP BY 
    data, emp_mkey, comp_mkey

I don't know what the reason is, I tried and didn't succeed.

I am using SQL Server 2008.

Upvotes: 0

Views: 43

Answers (1)

Dgan
Dgan

Reputation: 10285

try this:

As error say '#storedTemptable' is not there. Means '#storedTemptable' Table is not created you can't directly insert. Here i Created '#storedTemptable' table run time.

if object_id('tempdb..#storedTemptable') is not null
drop table #storedTemptable;


select distinct emp_mkey, data, COunt(*) as cnt into  #storedTemptable 
from
    (select * from Emp_mon_day where emp_mkey IN 
   (select emp_card_no from emp_mst where comp_mkey in
   (7,110)) and Year = 2016 and month = 2 ) s
unpivot
(
    data for day in ([Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9],[Day10],[Day11],[Day12],
                [Day13],[Day14],[Day15],[Day16],[Day17],[Day18],[Day19],[Day20],[Day21],[Day22],[Day23],
                [Day24],[Day25],[Day26],[Day27],[Day28],[Day29],[Day30]) 
)up GROUP BY data, emp_mkey, comp_mkey

Upvotes: 1

Related Questions