T-SQL – How to delete #TEMP table if exists

There is a powerful functionality to create temporary tables and use it later as a normal table.  Works especially well for data conversions to store stable reference lists  under 1000 rows.

Use it for State, Country, Currency, Commission code, business unit, currency cross reference.

Use this technique when do not have that information stored in existing tables and you do not have CREATE TABLE permissions on the server.

Use syntax below.  Notice two dots between tempDB and #tempCurrency.

You need to use tempDB qualifier for OBJECT_ID() to work properly.

IF OBJECT_ID(‘tempDB..#tempCurrency’,’U’) IS NOT NULL drop table #tempCurrency

. . .

CREATE TABLE #tempTable
(
[ttCurCode] CHAR(2) PRIMARY KEY,
[ttCRCD] CHAR(3) NOT NULL
)

. . .

INSERT INTO #tempCurrency([ttCurCode], [ttCRCD] ) VALUES
  (’01’, ‘USD’), (’02’, ‘AUD’), (’03’, ‘GBP’), (’04’, ‘CAD’), (’05’, ‘EUR’), (’06’, ‘EUR’)
, (’07’, ‘HKD’), (’08’, ‘EUR’), (’09’, ‘JPY’), (’10’, ‘EUR’), (’11’, ‘MXP’), (’12’, ‘KRW’)
, (’13’, ‘CNY’), (’14’, ‘CZK’), (’15’, ‘CHF’)