SQL IF Statement to Insert Into the same table

Question

I have a need to query a data set that is stored in two different SQL schemas depending on how we receive the data. I have created the two queries to look at dataset 1 and dataset 2 and it formats that data into two separate temp tables (in the same format).

I'm now trying to do the second step which is working out which temp table has been populated putting this data into a new temp table in order to move on to step 3.

To do this, I'm trying to create an IF statement to work out which dataset we receive but can't seem to get the query to work (even though I have seen solutions from other people's similar queries).

This is my code:

IF object_id('tempdb..#Final_Prem') IS NOT NULL 
BEGIN 
    DROP TABLE #Final_Prem 
END

DECLARE @Command varchar(500)

DECLARE @DS1 AS FLOAT
DECLARE @DS2 AS FLOAT

SET @DS1 = (SELECT SUM(PREM) FROM #Dataset1 )
SET @DS2 = (SELECT SUM(PREM) FROM #Dataset2 )

IF (@DS1 IS NULL OR @DS1 = 0)
BEGIN
    SET @Command = 'SELECT * INTO #Final_Prem FROM #Dataset2'
END ELSE BEGIN
    SET @Command = 'SELECT * INTO #Final_Prem FROM #Dataset1'
END

EXECUTE (@Command)

SELECT * FROM #Final_Prem

The error I keep getting is

Msg 208, Level 16, State 0, Line 18
Invalid object name '#Final_Prem'

Any help would be much appreciated, thanks

Instead of SET @DS1 = (SELECT SUM(PREM) FROM #Dataset1 ) use SELECT @DS1 = SUM(PREM) FROM #Dataset1 — JNevill
– JNevill, Commented Mar 12, 2020 at 16:02
Hi JNevill, when I remove the brackets and SELECT I get the Incorrect syntax near the keyword 'FROM'. error? — Carlos80
– Carlos80, Commented Mar 12, 2020 at 16:08

George Menoutis · Accepted Answer · 2020-03-12 16:58:21Z

1

You cannot use single-hash tables in dynamic queries. The session that runs the dynamic query is different than the outside one, and thus has no access to it.

If you are sure you will be running the proc/query once each time, you can change the # table to a ##table.

If you want to use both dynamic sql and concurrent runnings, you can save @@SPID into a variable and use it on the end of the ## table names.

edited Mar 12, 2020 at 16:58

answered Mar 12, 2020 at 16:06

George Menoutis

7,3003 gold badges23 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Brandt Over a year ago

I think you mean "single-hash" table?

Peter B · Accepted Answer · 2020-03-12 16:04:14Z

The query as a whole needs to compile before any of it can run, and it looks like the last line SELECT * FROM #Final_Prem is causing compilation to fail because the #Final_Prem table does not (yet) exist at that stage.

If you are using SSMS, then you can fix it by putting a line containing only GO after the EXECUTE statement. GO is a pseudo statement (not part of the SQL language) that chops the input into batches( pieces), which are compiled and run one by one, thus preventing such compilation issues.

Eric Brandt · Accepted Answer · 2020-03-12 16:28:04Z

You're having a scope issue.

A temporary table is only visible to the session where it's created. Your table #Final_Prem is created within the scope of the EXECUTE (@Command) statement, which, unfortunately, is it's own session, and is different from the scope of the calling procedure. As soon as that EXECUTE() is done, the temp table is dropped.

You have two options.

Create a real table.
Use a global temporary table (##Final_Prem instead of #Final_Prem).

Either way, the table will be accessible from both the inner and outer sessions.

Edit (response to comment): Code in comments is pretty hard to read, so here's what the code would look like using a global temp table:

IF object_id('tempdb..##Final_Prem') IS NOT NULL BEGIN DROP TABLE ##Final_Prem END

DECLARE @Command varchar(500)

DECLARE @DS1 AS FLOAT
DECLARE @DS2 AS FLOAT

SET @DS1 = (SELECT SUM(PREM) FROM ##Dataset1 )
SET @DS2 = (SELECT SUM(PREM) FROM ##Dataset2 )

IF (@DS1 IS NULL OR @DS1 = 0)
BEGIN
    SET @Command = 'SELECT * INTO ##Final_Prem FROM ##Dataset2'
END ELSE BEGIN3
    SET @Command = 'SELECT * INTO ##Final_Prem FROM ##Dataset1'
END

EXECUTE (@Command)

SELECT * FROM ##Final_Prem

I also changed the two Dataset tables to globals, because they're both accessed from both sessions, too. So you'd need to change them elsewhere, since they're not defined within this code block.

Hi Eric, thanks for your help. I have amended my query to look like this with the ## on the second argument. I was just wondering if I'm following your advice correctly? BEGIN SELECT * INTO #Final_Prem FROM #DS2 END ELSE BEGIN SELECT * INTO ##Final_Prem FROM #DS1 END

sacse · Accepted Answer · 2020-03-12 16:40:13Z

0

You can simply place the select statement inside the dynamic SQL too like below:

IF (@DS1 IS NULL OR @DS1 = 0)
BEGIN
    SET @Command = 'SELECT * INTO #Final_Prem FROM #Dataset2 SELECT * FROM #Final_Prem'
END ELSE BEGIN
    SET @Command = 'SELECT * INTO #Final_Prem FROM #Dataset1 SELECT * FROM #Final_Prem'
END

EXECUTE (@Command)

Please check the db<>fiddle here.

edited Mar 12, 2020 at 16:40

answered Mar 12, 2020 at 16:26

sacse

3,8272 gold badges19 silver badges27 bronze badges

1 Comment

Eric Brandt Over a year ago

But the two #Dataset tables aren't going to be available from inside the dynamic statement.

Collectives™ on Stack Overflow

SQL IF Statement to Insert Into the same table

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Related