lundi 27 mars 2017

IF... ELSE... two mutually exclusive inserts INTO #temptable

I need to insert either set A or set B of records into a #temptable, depending on certain condition

My pseudo-code:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

IF {some-condition}
  SELECT {columns}
  INTO #t1 
  FROM {some-big-table}
  WHERE {some-filter}
ELSE
  SELECT {columns}
  INTO #t1
  FROM {some-other-big-table}
  WHERE {some-other-filter}

The two SELECTs above are exclusive (guaranteed by the ELSE operator). However, SQL compiler tries to outsmart me and throws the following message:

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

My idea of "fixing" this is to create #t1 upfront and then executing a simple INSERT INTO (instead of SELECT... INTO). But I like minimalism and am wondering whether this can be achieved in an easier way i.e. without explicit CREATE TABLE #t1 upfront.

Btw why is it NOT giving me an error on a conditional DROP TABLE in the first line? Just wondering.

Aucun commentaire:

Enregistrer un commentaire