CODEWITHSUNDEEP
sql-servert-sqlc#-4.0
Khan
Khan

Reputation: 271

Temp table is present in the tempdb

I am trying to create a stored procedure inside which I am creating a temp table with some fieds(just for entity framework to identify the return fields else it returns only int) and When I am running my stored procedure #EmpTab is create and says that table is already exist no matter even if I delete this manually. Once I run sp then it says the same.

alter proc test
as
begin

IF OBJECT_ID('tempdb..#EmpTab ') IS NOT NULL 
BEGIN
    DROP TABLE #EmpTab 
END


create table #EmpTab (
ID int,
Name varchar(20),

);

select empId, EmpName into #EmpTab from employee;

select * from #EmpTab ;
drop table #EmpTab ;

end

Upvotes: 2

Views: 81

Answers (4)

Praveen ND
Praveen ND

Reputation: 560

Try to make use of below code :

    alter proc test
    as
    begin

    IF OBJECT_ID('tempdb..#EmpTab ') IS NOT NULL 
    BEGIN
        DROP TABLE #EmpTab 
    END


    create table #EmpTab (
    ID int,
    Name varchar(20),

    );

    Insert into #Emptab(ID,Name)
    select empId, EmpName from employee;

    select * from #EmpTab ;
    drop table #EmpTab ;

    end

Upvotes: 0

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

    IF you check your script you will find #EmpTab Is Getting Created Twice 
    1)1st time you create Temp Table.
    2)While Inserting Data INTO Temp Table.
    Both time you Create Same Temp_Table.

        CREATE proc test
        AS
        BEGIN

        SET NOCOUNT ON

        SELECT empId, EmpName INTO #EmpTab 
        FROM employee;

        SELECT * FROM #EmpTab ;
        DROP TABLE #EmpTab ;

        SET NOCOUNT OFF       
        END

Upvotes: 0

PP006
PP006

Reputation: 709

Use Insert into ,since already table has been created

Insert into #Emptab(ID,Name)
select empId, EmpName from employee;

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452947

SELECT ... INTO creates the temp table. So does CREATE TABLE.

Either get rid of the explicit CREATE TABLE or replace the SELECT ... INTO with an INSERT if you want to retain the explicit create.

BTW there is no need for a temp table here anyway. The whole procedure code could be replaced with 

select empId, EmpName from employee;

Is this just a test procedure with dummy code?

Upvotes: 4

Related Questions