CODEWITHSUNDEEP
sqlsql-serverinformation-schema
Dizzy49
Dizzy49

Reputation: 1520

SQL Server : stating temp table exists, but it's not in INFORMATION_SCHEMA

I'm using the following to check for and create a table:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'tempdb' AND TABLE_NAME LIKE '#tmp_items%')
    CREATE TABLE #tmp_items 
    (
        id               INT IDENTITY(1,1),
        inv_mast_uid     INT DEFAULT NULL,
        src_invoice      BIT DEFAULT NULL,
        src_invoice_date DATETIME DEFAULT NULL,
        src_order        BIT DEFAULT NULL,
        src_order_date   DATETIME DEFAULT NULL
    )
ELSE
    TRUNCATE TABLE #tmp_items

I get the following error:

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

If it existed, it should have truncated the table....

When I look at INFORMATION_SCHEMA, I don't see anything:

SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES

Returns:

ssb
UTIL
mbl
DataSync
dbo

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%tmp_items%'

Returns nothing.

It doesn't make sense, and I'm at a loss... I have even disconnected from the server (which should have purged the temp tables) and reconnected and still get the same error.

Am I missing something?

Upvotes: 0

Views: 470

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You are going to struggle with conditionally truncating or creating a temp table like this no matter how you slice it. Change this to drop the temp table if it exists and then always create it. This is much simpler.

IF OBJECT_ID('tempdb..#tmp_items%') is not null
    drop table #tmp_items

CREATE TABLE #tmp_items 
(
    id                  int         identity(1,1),
    inv_mast_uid        int         DEFAULT NULL,
    src_invoice         bit         DEFAULT NULL,
    src_invoice_date    datetime    DEFAULT NULL,
    src_order           bit         DEFAULT NULL,
    src_order_date      datetime    DEFAULT NULL
)

Upvotes: 3

Related Questions