CODEWITHSUNDEEP
sql-servert-sqlloopscursor
Mike Marks
Mike Marks

Reputation: 10129

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

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

Upvotes: 0

Views: 1730

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 3

Related Questions