Select into a temp table from within a while loop (cursor)

Question

I'm trying to essentially "union" the result sets of my cursor. Since I can't figure out how to "union" them, I figured, hey, why not store the results into a temp table, and then just select * from that temp table? Problem is, when I run this code, it says that my temp table already exists. I think when I do select into, that tries to create a new temp table each time. How do I insert into a temp table without using select into?

drop table #mikemarks
declare db_cursor CURSOR FOR SELECT jobid from cxxxxxxx.Campaign_Data_Extension_Names
declare @jobid int
open db_cursor
fetch next from db_cursor into @jobid while @@FETCH_STATUS = 0
begin
    select j.jobid as 'JobID', j.subscriberkey as 'EmailAddress', j.createddate as 'EventDate', jc.errorcode as 'ErrorCode', jc.Description as 'ErrorCodeDescription' into #mikemarks
    from jobsubscribererror j with (nolock)
    inner join jobsubscribererrorcode jc on j.errorcodeid = jc.errorcodeid
    where j.jobid = @jobid
    fetch next from db_cursor into @jobid
end
close db_cursor
deallocate db_cursor

No need for looping to do inserts. This should be set based. And be careful with those NOLOCK hints. It is much more than just dirty reads. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Dec 01 '15 at 21:45
@KiwiPiet I am iterating through the SELECT statement for each JobID that is returned from the table "Campaign_Data_Extension_Names". — Mike Marks, Dec 01 '15 at 21:45
@SeanLange I need to run the inside select statement for each JobID that's in my "Campaign_Data_Extension_Names" table.. hence the cursor. I'm new to cursors and am open to alternatives — Mike Marks, Dec 01 '15 at 21:47
You don't need a separate insert for each row. You certainly don't need a cursor. Do you need these results in a temp table? From your description it is hard to figure out what you are trying to do. — Sean Lange, Dec 01 '15 at 21:49
@SeanLange thanks for the response! So, the SELECT statement - you see it? I need to run that select statement for each JobID value within the table "Campaign_Data_Extension_Names". This query above runs fine, but SQL produces as many result sets as there are JobID values. I can work with this but would prefer one large result set than many result sets. — Mike Marks, Dec 01 '15 at 21:52
@MikeMarks, cursors are [considered bad practice](http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server) and should be avoided at all cost. — KiwiPiet, Dec 01 '15 at 21:55

score 3 · Accepted Answer · answered Dec 01 '15 at 21:51

If you need this in a temp table you can do this in a single statement instead of looping. Something like this.

select j.jobid as 'JobID'
    , j.subscriberkey as 'EmailAddress'
    , j.createddate as 'EventDate'
    , jc.errorcode as 'ErrorCode'
    , jc.Description as 'ErrorCodeDescription' 
into #mikemarks
from jobsubscribererror j
inner join jobsubscribererrorcode jc on j.errorcodeid = jc.errorcodeid
join cxxxxxxx.Campaign_Data_Extension_Names n on j.jobid = n.jobid

Thank you I'll give that a shot! – Mike Marks Dec 01 '15 at 21:53 — Mike Marks, Dec 01 '15 at 21:53

Select into a temp table from within a while loop (cursor)

1 Answers1