if condition select into temp table

Question

Post reply

if condition select into temp table

girl_bj

SSCommitted

Points: 1731

August 18, 2023 at 3:02 am

#4264143

Hi, I have a query where I search and provide the result.

select * into #a from(
Select ID, 1 as counter from TableA where ID=@id
union all
select ID, 2 as counter from TableB where ID=@id)a

declare @tocheck integer
set @tocheck =(select tocheck from #a)

if @tocheck=1
DROP TABLE IF EXISTS #partA
begin
insert into #partA (ID)
select .... 
end

if @tocheck=2
DROP TABLE IF EXISTS #partB
begin
insert into #partB (ID)
select .... 
end

select * from #partA
union all
select * from #partB

Let say the the ID result is 1, I keep getting error "Invalid object name '#partA'." I have @tocheck condition with different database more than 2. So I can't use ELSE option. Any solution?

Let say the the ID result is 1, I keep getting error "Invalid object name '#partA'." I have @tocheck condition with different database more than 2. So I can't use ELSE option. Any solution?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

if @tocheck=1

DROP TABLE IF EXISTS #partA

begin

insert into #partA (ID) <- this table no longer exists here if @tocheck=1 !!! ( you are not using select ... into #partA )

select ....

end

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Maybe you could switch the logic around a bit?

DECLARE @tocheck INTEGER;

SET @tocheck =
(
    SELECT tocheck FROM #a
);

DROP TABLE IF EXISTS #part;

CREATE TABLE #part(Col1, ..., );

IF @tocheck = 1
    INSERT INTO #part
    (
        ID
    )
    SELECT ...;

IF @tocheck = 2
    INSERT INTO #part
    (
        ID
    )
    SELECT ...;

SELECT *
FROM #part;

This reply was modified 1 year, 3 months ago by Phil Parkin. Reason: Fixed code - had missed the CREATE TABLE

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

Your approach to SQL is wrong. This is a declarative language but you are writing code as if you were still doing Fortran with file systems. Instead of scratch tapes, you have temp tables. Instead of dismounting a magnetic tape, you drop a table. Why materialize things which can be kept in a view? Let me answer my own question: you're not writing SQL , but hiding a very 1960s Fortran program and SQL.

Try using a CASE expression instead of procedural flow control . There is also no such thing as a magical universal generic ID and RDBMS; it has to be the identifier of something in particular.

It's also interesting to see the samekey on two separate tables, since in a properly designed schema, these tables would represent totally different entities.

Another problem we have is that you bother to post any DDL, not even a little skeleton. Consider this view instead of physically writing a table to materialized storage:

CREATE VIEW Foobar

AS

SELECT vague_id, ..

FROM Alpha

UNION ALL

SELECT vague_id, ..

FROM Beta;

Without DDL, it's impossible to accurately give you any help. After 40 years of this, I've learned that I always guess the posters intent wrongly.

Please post DDL and follow ANSI/ISO standards when asking for help.

kajaldigital8700@gmail.com SSC Rookie Points: 43 More actions · Answer 4

The error you're encountering, "Invalid object name '#partA'," is likely due to the fact that you're trying to drop or insert data into temporary tables conditionally based on the value of @tocheck. Temporary tables like #partA and #partB are only accessible within the scope of the session that created them. Therefore, if @tocheck evaluates to 2, the code for #partA is never executed, and #partA is not created in that session. As a result, when you try to reference #partA later in your code, you get an "Invalid object name" error.

To work around this issue, you can create both #partA and #partB tables at the beginning of your script, and then conditionally populate them based on the value of @tocheck. Here's how you can modify your code:

-- Create both temporary tables at the beginning CREATE TABLE #partA (ID INT); CREATE TABLE #partB (ID INT); -- Your existing code to populate #a and set @tocheck if @tocheck=1 begin -- Insert data into #partA insert into #partA (ID) select .... end if @tocheck=2 begin -- Insert data into #partB insert into #partB (ID) select .... end -- Your existing code to select data from #partA and #partB select * from #partA union all select * from #partB

By creating both temporary tables #partA and #partB at the beginning of your script, you ensure that they exist in the session regardless of the value of @tocheck. You can then conditionally populate them based on the value of @tocheck, and later select data from them without encountering "Invalid object name" errors.