Insert...Exec and Temp Table rows repeating

Question

Insert...Exec and Temp Table rows repeating

AVB

SSChampion

Points: 11418
More actions
December 29, 2006 at 12:17 pm

#115548

I created a Temp Table to insert the output of an Proc. When I do this it returns only about 7 rows (which is expected) but when I try to narrow the results with a where clause it returns 7 rows all of which contain the same data.
Create #Table (col int, col2, int)
Insert #Table (col,col20
Exec s_Proc
select * from #Table where col = 65 <-- Produces multiple rows same data
select * from #Table <-- Mulitple Rows different data

Is this normal?

Viewing 9 posts - 1 through 8 (of 8 total)

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

DaveItz SSCrazy Points: 2146 More actions · Answer 1

Could it be that the stored procedure is really returning duplicate rows but you can't tell because it looks distinct with the current sort?

There may be a Join you haven't considered.

Try using

select * from #table order by col

AVB SSChampion Points: 11418 More actions · Answer 2

Distinct is not specified but there are a lot of joins. However it still doesn't make sense.

When I run the procedure there are no duplicate rows. The output from running Exec s_Proc is the same as Insert #Table .... Exec s_Proc select * from #table (except when I use the Where clause)

DaveItz SSCrazy Points: 2146 More actions · Answer 3

That's very strange.

The only thing else I can think of is that the stored procedure returns multiple resultsets.

create procedure usp_MyStoredProc
as
begin
   select *
   from(select 1, 1 union all
        select 2, 2 union all
        select 3, 3 union all
        select 4, 4 union all
        select 5, 5 union all
        select 6, 6 union all
        select 7, 7) as t(col, col2)

   select *
   from(select 1, 1 union all
        select 2, 2 union all
        select 3, 3 union all
        select 4, 4 union all
        select 5, 5 union all
        select 6, 6 union all
        select 7, 7) as t(col, col2)
end

but then when you try the Insert you'd see

(14 row(s) affected)

There's no explaining it. Once the results are in the temporary table that should be it.

AVB SSChampion Points: 11418 More actions · Answer 4

I missed your select statment. I tried that and "ordered by" the column I'm including in my where clause. When I ran that it returned 261 rows many which are in groups of duplicate rows.

The SP is a Select statment with a bunch of UNION Selects. It has an Order By clause at the end of all of it. That's it. However due to various reasons some of the Select statements in the Union won't return data based on parameters supplied to the proc.

DaveItz SSCrazy Points: 2146 More actions · Answer 5

Well I'm glad you worked it out.

Good luck with the rest of your fixes.

AVB SSChampion Points: 11418 More actions · Answer 6

Well not really.... now I know that it's just really screwy. Thanks for the replies it helped to return some interesting results. I'll poke around some more and post when I find out what's up. Thanks again.

AVB SSChampion Points: 11418 More actions · Answer 7

Ok I found the problem... I didn't drop the table and it inserted the result set a couple of times. This happened prior to me doing a select with a where clause. I have corrected it by dropping the table in each run. THis was it clears the result set each time. But this brings me to another question....

Can you delete the data in a temp table? If so would this be more efficent than dropping it?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Lynn Pettis

SSC Guru

Points: 442467

January 2, 2007 at 2:48 pm

#680841

truncate table #tablename