CODEWITHSUNDEEP
sql-servert-sql
Bozhidar Stoinev
Bozhidar Stoinev

Reputation: 1

Unable to drop a temporary table

I have a stored procedure which uses a temporary table. The thing is that I've tried to use the temporary table more than one time in different SELECT INTO statements. Of course, before issuing the next statement I have issued a DROP #TempTableName and then issue the SELECT INTO statement. Apparently this DROP statement isn't enough since the next SELECT INTO statement complains that the object #TempTableName already exist - SSMS output is:

Msg 2714, Level 16, State 1, Procedure SYNC_SpreadMembers, Line 23
There is already an object named '#MM_SYNC_MEMBERS' in the database.

And here is my T-SQL code:

CREATE PROCEDURE SYNC_SpreadMembers
AS
BEGIN
    BEGIN

        -- Member
        IF (OBJECT_ID('tempdb..#MM_SYNC_MEMBERS') IS NOT NULL) 
            DROP TABLE #MM_SYNC_MEMBERS;
        -- Imported members
        SELECT DISTINCT MemberInr INTO #MM_SYNC_MEMBERS FROM
            (
                SELECT DISTINCT DmInr AS MemberInr FROM MM_SYNC_EBOLIGWS WHERE NOT DmInr IS NULL
                UNION 
                SELECT DISTINCT AmInr AS MemberInr FROM MM_SYNC_EBOLIGWS WHERE NOT AmInr IS NULL
            ) MemberHeap
        ;                   
        DELETE #MM_SYNC_MEMBERS FROM #MM_SYNC_MEMBERS Sync INNER JOIN MM_Member Member ON Sync.MemberInr = Member.InteressentNr;
        INSERT INTO MM_Member(InteressentNr) SELECT MemberInr FROM #MM_SYNC_MEMBERS;
    END

    -- Hardcoded members
    DROP TABLE #MM_SYNC_MEMBERS;
    SELECT DISTINCT InteressentNr AS MemberInr INTO #MM_SYNC_MEMBERS FROM MM_SYNC_HardcodedMemberRoles;
    DELETE #MM_SYNC_MEMBERS FROM #MM_SYNC_MEMBERS Sync INNER JOIN MM_Member Member ON Sync.MemberInr = Member.InteressentNr;
    INSERT INTO MM_Member(InteressentNr) SELECT MemberInr FROM #MM_SYNC_MEMBERS;

    -- MemberRole
    -- Area Managers
    DELETE MM_MemberRole;
    INSERT INTO MM_MemberRole(MemberSid, RoleSid)
        SELECT DISTINCT Member.[Sid], (SELECT [Sid] FROM MM_Role WHERE Cipher LIKE 'AMA')
        FROM MM_SYNC_EBOLIGWS Sync
            INNER JOIN MM_Member Member ON Sync.AmInr = Member.InteressentNr
        WHERE Sync.AmInr IS NOT NULL
    ;
    -- Department Managers
    INSERT INTO MM_MemberRole(MemberSid, RoleSid)
        SELECT DISTINCT Member.[Sid], (SELECT Sid FROM MM_Role WHERE Cipher LIKE 'DM')
        FROM MM_SYNC_EBOLIGWS Sync
            INNER JOIN MM_Member Member ON Sync.DmInr = Member.InteressentNr
        WHERE Sync.DmInr IS NOT NULL
    ;

    -- Hardcoded Roles
    INSERT INTO MM_MemberRole(MemberSid, RoleSid)
        SELECT Member.Sid, Roles.Sid 
        FROM MM_SYNC_HardcodedMemberRoles HCR 
            INNER JOIN MM_Member Member ON HCR.InteressentNr = Member.InteressentNr
            INNER JOIN MM_Role Roles ON HCR.RoleCipher = Roles.Cipher
    ;
END
GO

Upvotes: 0

Views: 2142

Answers (4)

Rahul Sharma
Rahul Sharma

Reputation: 11

why done you create structure of #MM_SYNC_MEMBERS globally and then you can delete or insert data in temp according to conditions.

EX-

create table #Temp (name varchar(20))

IF(condtion1) insert into temp (or delete) else if(condition2) insert to temp .........

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

T-SQL is a very simple language - it basically compiles all of the code in the current scope/batch as soon as possible. At various times (such as when a new table is created) it will recompile the batch.

The error is actually being thrown when it does the recompile immediately after you first create the new temp table. At that point, when it tries to recompile the later statement that also tries to create a temp table with the same name, it produces the error.

It doesn't wait to see whether the normal flow of execution (including control flow) will prevent an error occurring when the statement is reached. E.g. this produces a similar error:

create table #Blah (ID int)
if 1 = 0
begin
    create table #Blah (Foo int)
end

Even though we can look at it and know that no harm would actually occur

Msg 2714, Level 16, State 1, Line 4

There is already an object named '#Blah' in the database.

Upvotes: 2

Nick N.
Nick N.

Reputation: 13559

Temporary tables are tied to a connection. So when the connection is dropped, the temporary table is dropped.

So it won't be dropped in the middle of the stored procedure.

This will be tied to the instance it is in, it could fit your needs:

DECLARE @TemporaryTable TABLE 
(
      id int,
      name nvarchar(50)
)

Extra: Maybe you could also have a look at CTE's as it may be a solution for your problem: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Upvotes: 0

Bill Gregg
Bill Gregg

Reputation: 7147

Since the table has the same columns in both inserts, why not just create the table once, and instead of dropping it:

 TRUNCATE TABLE #MM_SYNC_MEMBERS

Upvotes: 0

Related Questions