none
Dropping and recreating table in SQL 2008

    Question

  •  

    Hi,

    We seem to hit a race condition at times when trying to drop and recreate a table inside an SP. The code is something like:

     

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      set @cmd = N'drop Table '+@stagingTableName
      exec(@cmd)
     end

     if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      --create staging table
      set @cmd = N'select * into '+@stagingTableName+' from '+@CacheTableName+' where 1=2'; 

      exec(@cmd)

     

     

    Occasionally we get an error 2714 - table already exists.

     

    Is this a race condition between the drop and the "select into"? If so what is the best way to avoid it?

    Thanks

    Darshat

    Monday, November 10, 2008 8:40 PM

Answers

  •  

    Well the only thing that would come in my mind would be that if you are precise in defining the schema name in the first place (EXISTS) you can also do that when doing the Select into and drop statement.

     

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      set @cmd = N'drop Table dbo.'+@stagingTableName
      exec(@cmd)
     end

     if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      --create staging table
      set @cmd = N'select * into dbo.'+@stagingTableName+' from '+@CacheTableName+' where 1=2'; 

      exec(@cmd)

     

     

    Jens K. Suessmeyer
    Monday, November 10, 2008 11:04 PM
  • ...or you could use real #temp tables with DROP statement. I'm a little out of date with the variable tables but isn't a variable table dropped by itself?

     

    A.D.T

     

    Monday, November 10, 2008 11:12 PM

All replies

  •  

    Well the only thing that would come in my mind would be that if you are precise in defining the schema name in the first place (EXISTS) you can also do that when doing the Select into and drop statement.

     

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      set @cmd = N'drop Table dbo.'+@stagingTableName
      exec(@cmd)
     end

     if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].'+@stagingTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     begin
      --create staging table
      set @cmd = N'select * into dbo.'+@stagingTableName+' from '+@CacheTableName+' where 1=2'; 

      exec(@cmd)

     

     

    Jens K. Suessmeyer
    Monday, November 10, 2008 11:04 PM
  • ...or you could use real #temp tables with DROP statement. I'm a little out of date with the variable tables but isn't a variable table dropped by itself?

     

    A.D.T

     

    Monday, November 10, 2008 11:12 PM
  •  

    Thanks for the info. When I went back and looked at the tables, they were created in a schema that had the name of the machine as prefix. <domain\machinename$>. The process that creates these runs in the machine account. I'm trying to narrow down why this schema got picked instead of dbo.
    Monday, November 10, 2008 11:51 PM