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

debugcn Published at Dev

Mike Marks

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

Sean Lange

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-15

Comments

0 comments

From Dev

Related Related

Article

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

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

MySQL: Cursor for select from temp table

Select as in SQL while selecting from Temp table

Temp Table Populate WHILE/Cursor

Select from two temp tables into another temp table

Select * into Temp Table from Dynamic SQL Results

Select into temp table in PostgreSQL?

select data from #Temp table after #temp table create in another query in asp.net c#

StringIndexOutOfBoundsException error from stringbuilder within a while loop

Passing variable from within while loop to popupdiv

Can I use SELECT from dataframe instead of creating this temp table?

Get a sum from a temp table into my select query

Can I use SELECT from dataframe instead of creating this temp table?

while loop within while loop

IF statement from a temp table

Select Into #Temp From Select Query

Select from same table, within IF statement

In Sql Server, how do you put value from cursor into temp table?

Insert data from a table to a temporary table, and then select from the temp table specific rows

Declare a cursor within FOR loop ORACLE

Updating columns within a Cursor For Loop

Mysql select query with temp table

Mysql select query with temp table

Add new columns to a SQL Server table within a while loop

Slow Performance within While Loop when Updating MYSQL Table

Add new columns to a SQL Server table within a while loop

create table temp2 select * from temp1 is not taking all the properties of the source table in hive 0.14

Using the data from a for loop as a table in the From in a Select

Declare a Cursor From a Table as Variable (In the Select-Statement)

Select values from table and show it by while?