IF Exists doesn't work properly

Question

I need some help with this query, I'm trying to run it but all I keep getting is

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

Can you please advise on a workaround so that the data will go in by using the nested IF Exists statement ? I've also tried using an ELSE IF EXISTS after the first IF EXISTS but I get the same error.

CREATE TABLE dbo.PostPivotTbl (
TestDate VARCHAR(100)
 );

 GO

INSERT INTO dbo.PostPivotTbl
VALUES ('17/Jul/2014'),
'10/May/2014' ) 



IF EXISTS (
SELECT TestDate
FROM dbo.PostPivotTbl
WHERE TestDate LIKE '[1-9]%' AND TestDate NOT LIKE '0%' AND TestDate NOT LIKE '%/%'
  )

BEGIN

IF OBJECT_ID('tempdb..#PreFixedData1', 'u') IS NOT NULL DROP TABLE #PreFixedData1

 SELECT
 CAST(TestDate AS INT) AS 'TestDate'
 INTO #PreFixedData1
 FROM dbo.PostPivotTbl

 END

 ELSE

    BEGIN


      IF EXISTS(

             SELECT TestDate
             FROM dbo.PostPivotTbl
             WHERE TestDate 
  LIKE '%2013%' AND TestDate LIKE '%-%' OR TestDate LIKE '%2014%' AND TestDate LIKE '%-%' 

  OR
        TestDate LIKE '%2013%' AND TestDate LIKE '%/%' OR TestDate LIKE '%2014%' AND      
        TestDateLIKE '%/%'   
   OR TestDate LIKE '%2012%' AND TestDate LIKE '%/%'
   OR
TestDate LIKE '%Jan%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Feb%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Mar%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Apr%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%May%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Jun%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Jul%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Aug%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Sep%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Oct%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Nov%' AND TestDate LIKE '%-%'
OR
TestDate LIKE '%Dec%' AND TestDate LIKE '%-%'
)

BEGIN

IF OBJECT_ID('tempdb..#PreFixedData1', 'u') IS NOT NULL DROP TABLE #PreFixedData1


SELECT
CAST(TestDate AS DATETIME) AS 'TestDate'
INTO #PreFixedData1
FROM dbo.PostPivotTbl

    END
END

possible duplicate of [How do I conditionally create a table in Sybase (TSQL)?](http://stackoverflow.com/questions/307942/how-do-i-conditionally-create-a-table-in-sybase-tsql) — ninesided, Sep 02 '14 at 09:23
or possible duplicate of [this](http://stackoverflow.com/questions/12408599/t-sql-with-if-logic-not-working-with-temp-table?rq=1) — ninesided, Sep 02 '14 at 09:28
@ninesided - No it's not, that guy's issue is different, he's unable to create a table, I am creating the table, just can't query from it once the data is in... — Claudio, Sep 02 '14 at 09:34

score 3 · Answer 1 · edited Jul 29 '20 at 18:43

3

This is nothing to do with the EXISTS clause (which looks very messy by the way), it is to do with the way SQL Server compiles the statements. You can recreate this simply with:

IF 1 = 0 
BEGIN
    SELECT Col = 1
    INTO #T;
END
ELSE
BEGIN
    SELECT Col = 2
    INTO #T;
END

Which gives the error:

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

Even though only one branch of the IF statement can every be followed, SQL Server compiles them both, so when it compiles the second branch it estabilishes that #T will exists, so throws a compilation error.

The work arround is to create the table once, and use INSERT [Table] SELECT..:

CREATE TABLE #PreFixedData1 (TestDateInt INT, TestDate DATETIME);

IF ...
    INSERT #PreFixedData1 (TestDateInt)
    SELECT ....

ELSE 
    INSERT #PreFixedData1 (TestDate)
    SELECT ...

It is beyond the scope of the question, but I have no idea why you would create a column that is VARCHAR(100) and then insert dates to it. Keep dates and dates then you don't need to do all sorts of bizarre comparisons to work out if it is a valid date. I am also not sure why you want to conditionally create the same table but with a different datatype for the column depending on the data in the table.

As a further aside, using string literals as column aliases is on the deprecation list (SELECT Column AS 'Alias')

edited Jul 29 '20 at 18:43

Community

1
1

answered Sep 02 '14 at 09:35

GarethD

68,045
10
83
123

I am trying to find a different way to cast data types that would originally come from an excel file which would have datetime columns in VARCHAR or NVARCHAR, this is a way to counteract the precedence data types issue that I came across last week. By the way, I see you're in Manchester like me :D cool stuff, also I'll try your solution now and let you know how it goes, thanks. – Claudio Sep 02 '14 at 09:41
What version of SQL Server are you using? – GarethD Sep 02 '14 at 09:50
Worked a treat mate, modified your solution a bit but it did work, thanks for the tip ! – Claudio Sep 02 '14 at 09:53
I'm using SQL Server 2008 R2 – Claudio Sep 02 '14 at 09:54
No worries. If you were using SQL Server 2012, I would have suggested the `TRY_CONVERT` function, then all the checking would have been redundant. Oh well, if you have managed to get to a working solution that is good enough for me. – GarethD Sep 02 '14 at 10:11

IF Exists doesn't work properly

1 Answers1