CODEWITHSUNDEEP
sql-servert-sql
SMITH
SMITH

Reputation: 75

T-SQL Insert into temp table from another temp table

I am trying to acheive like this at the end of the procedure i need all the rows in one temp table How can I accomplish this

if @i > 1
begin
select * from into #tempTbl1 from payments
where method = 'test1'
end 
else
begin
select * from into #tempTbl2 from payments
where method = 'test1'
end

insert into #tempTbl1 select * from #tempTbl2

select * from #tempTbl1

Upvotes: 0

Views: 8648

Answers (2)

Vinnie
Vinnie

Reputation: 3929

The problem you have here is that based on your IF/ELSE you will never had both tables. Your final INSERT INTO requires that both tables exist. You may need to create the objects before hand in the stored proc before you try to populate, then insert into the tables.

This also begs the question, if you're going to later insert everything in #tempTbl1 anyways, which is created in the SELECT INTO statement, why have the #tempTbl2 in the first place?

create procedure dbo.testing
(@i int)
AS
if @i > 1
    begin
        print 'In condition 1'
        select * 
        into #tempTbl1 
        from payments
        where method = 'test1'
    end 
else
    begin
        print 'In condition 2'
        select * 
        into #tempTbl2 
        from payments
        where method = 'test1'
    end

print 'Made it out of the if else'

insert into #tempTbl1 
select * 
from #tempTbl2

--  Never gets this far...
print 'In the final select'

select * 
from #tempTbl1

If you're committed to this method, then you may need to check to see if the table exists:

IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.#tempTbl1') AND type in (N'U'))
print 'Table is there'

Update based on comments

Based on your comments, this will work. The SELECT...INTO statement you originally posted lets you create a table based on the data types of the columns your selecting from, but the destination table can't already exist. If you define the structure you're going to insert into beforehand, you can have the two conditions evaluate and end up with a single table as the result.

(Note - my "payments" table only had two columns, "method" and "col2". You would want to specify the columns you need in the CREATE TABLE and the SELECT)

create procedure dbo.testing
(@i int)
AS
create table #tempTbl1
(method varchar(10)
, col2 int)

if @i > 1
    begin
        insert into dbo.#tempTbl1
        select method, col2 
        from payments
        where method = 'test1'
    end 
else
    begin
        insert into dbo.#tempTbl1
        select method, col2 
        from payments
        where method = 'test1'
    end

select * 
from #tempTbl1

Upvotes: 0

user2175297
user2175297

Reputation:

despite the previous logic issue, to simplely get all rows from both temp tables , use UNION:

select * from #tempTbl1  
UNION ALL  
SELECT * from #tempTbl2

Upvotes: 1

Related Questions