CODEWITHSUNDEEP
sqlsql-serverloopsdrop-table
Tomek Tarczynski
Tomek Tarczynski

Reputation: 2785

Double loop with drop table in MSSQL

I've while loop inside while loop and both loops do some modifications of one table. I receive an error that table already exists. Below simple example of this problem. Could someone clarify why this doesn't work? I know how to bypass this problem in this particular case, nevertheless I'd like to understand where did it come from.

CREATE TABLE #a(
  ID int)

DECLARE @i INT
DECLARE @j INT  

SET @i = 1
SET @j = 1

WHILE @i < 10
BEGIN

    SELECT  *
    INTO #b
    FROM  #a

    DROP TABLE #b

    WHILE @j < 10 BEGIN
        SELECT  *
        INTO #b
        FROM  #a

        DROP TABLE #b

        SET @j = @j + 1
    END

  SET @i = @i + 1
END

Upvotes: 3

Views: 1161

Answers (1)

SqlZim
SqlZim

Reputation: 38023

As Sean Lange pointed out, you can probably do whatever your doing without loops.

You cannot have two statements in the same procedure that create a temp table with the same name. This is a leftover from SQL 6.5 which did not have deferred name resolution.

Instead of using select into, use create table + insert.

Instead of dropping and recreating the same table with the same schema, use truncate table.

Answer by Erland Sommarskog on MSDN Social

create table #a(ID int);
create table #b(ID int);
declare @i int;
declare @j int;
set @i = 1;
set @j = 1;
while @i < 10
  begin;
    insert into #b (id)
      select id
      from #a;

    truncate table #b;

    while @j < 10
      begin;
        insert into #b (id)
          select id
          from #a

        truncate table #b;

        set @j = @j + 1;
      end;
    set @i = @i + 1;
  end;

Upvotes: 1

Related Questions