Cursor Output

Question

Cursor Output

Ronnie Jones

Hall of Fame

Points: 3377
More actions
July 28, 2010 at 10:09 pm

#238284

Hello All,
I've created a cursor that works fairly well. And I use a Print statement which prints out the results of my cursor. What I'd like to do is store the results of the cursor into a table. I've tried using a select into tablename but since it outputs one record at a time, when it goes back to check the next record, the script bombs complaining that the table already exists.
Does anybody know a way to store the output of a cursor into a table?
Thanks in advance for any help.
Ronnie

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

It would be better to avoid the cursor altogether. If you post according to the suggestions in the article at the first link in my signature below, someone will likely be able to show you how to replace the cursor with highly effecient set based code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Without a doubt best to avoid the cursor here, *but* there are ways to achieve the task if that's not possible for some reason.

Option 1 is to FETCH from the cursor into variables, and then do a normal INSERT, but that's a two-step operation.

There is a (very nasty) one-step global cursor + dynamic sql workaround, which Jeff is just going to love:

SET NOCOUNT ON;

DECLARE cur_ProductIDs

CURSOR GLOBAL

FORWARD_ONLY

STATIC

READ_ONLY

TYPE_WARNING

FOR

SELECT ProductID

FROM Production.Product

WHERE ProductID BETWEEN 300 AND 330;

OPEN cur_ProductIDs;

DECLARE @ProductIDs

TABLE (

product_id INTEGER PRIMARY KEY,

new_value INTEGER NOT NULL

);

WHILE (1 = 1)

BEGIN

INSERT @ProductIDs

(product_id, new_value)

EXECUTE (

'

DECLARE @ProductID INTEGER;

FETCH NEXT

FROM cur_ProductIDs INTO @ProductID

IF @@FETCH_STATUS = 0

SELECT product_id = @ProductID,

new_value = @ProductID * (-4)

'

);

IF (@@FETCH_STATUS = -1) BREAK;

END;

CLOSE cur_ProductIDs; DEALLOCATE cur_ProductIDs;

SELECT *

FROM @ProductIDs

ORDER BY

product_id;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Paul White NZ (7/29/2010)
There is a (very nasty) one-step global cursor + dynamic sql workaround, which Jeff is just going to love:

Heh... I AM curious why you'd show someone how to shoot themselves in the face once they've told you that they have a loaded gun.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

Jeff Moden (7/29/2010)
Heh... I AM curious why you'd show someone how to shoot themselves in the face once they've told you that they have a loaded gun.

Um, I was bored? :laugh:

Yeah it's dumb - I am slightly amazed it even runs, but there you go.

Call it a dangerous curiosity.

BTW - check your email!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Paul White NZ (7/29/2010)
Jeff Moden (7/29/2010)
Heh... I AM curious why you'd show someone how to shoot themselves in the face once they've told you that they have a loaded gun.
Um, I was bored? :laugh:
Yeah it's dumb - I am slightly amazed it even runs, but there you go.
Call it a dangerous curiosity.
BTW - check your email!

It's pretty inspired, in its evil-ness. I, for one, approve :hehe:

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Nevyn SSChampion Points: 14210 More actions · Answer 6

Jeff Moden (7/29/2010)
Paul White NZ (7/29/2010)
There is a (very nasty) one-step global cursor + dynamic sql workaround, which Jeff is just going to love:
Heh... I AM curious why you'd show someone how to shoot themselves in the face once they've told you that they have a loaded gun.

Well, I mean, who else would you teach it too? Teaching someone without a loaded gun how to shoot themselves in the face is just mean. They could never apply what they learned ...

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 7

I thought I would include my code which might further help:

declare

@GroupName varchar(255),

@ServerName varchar(15),

@AccountName varchar(255),

@ServerID int, @rowsfound int

declare grpCursor cursor for

select TOP (100) grp.localGroupName0, sys.Netbios_Name0, wittSrv.server_id,

MemberName = case substring(grp.MemberName0,1,len(sys.netbios_name0))

when sys.Netbios_Name0 then '*local*' + substring(grp.MemberName0,len

(sys.netbios_name0) + 1,len(grp.MemberName0))

else grp.MemberName0

end

from v_gs_wshsms_localgroupmembers grp

join v_r_system_valid sys on sys.resourceid = grp.resourceid

join [witt_info].dbo.servers_v wittSrv on sys.netbios_name0 = wittSrv.server_name

order by sys.netbios_name0, grp.localgroupname0, grp.membername0

open grpCursor

Fetch next from grpCursor into @GroupName, @ServerName, @ServerID, @AccountName

while @@Fetch_Status = 0

begin

select @rowsfound = count(*) from sox_audit.dbo.arttemp art

where art.ito_group = @GroupName and art.mech_id = @AccountName and

art.server_id = @ServerID

if @rowsfound > 0

begin

print @ServerID

print ':' + @ServerName + ' : ' + @AccountName + ' Found'

end

else

print @ServerName + ' : ' + @AccountName + ' not found'

Fetch next from grpCursor into @GroupName, @ServerName, @ServerID, @AccountName

end

close grpCursor

deallocate grpCursor

The print works fine but I want to store the output in a table

Thanks,

Ronnie

Nevyn SSChampion Points: 14210 More actions · Answer 8

Do you need both the print AND the data stored in a table, or do you need the data stored in a table INSTEAD? What are you using the prints for? Just asking because someone will probably be giving you a non-cursor version of this that runs much faster, and they'll need to know what \output you require.

Should the table just store a string of the message you print, or would you like other information about the row stored?

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 9

Thanks for the reply!

I would like to store the data in a table so that I can further manipulate it later. I was using the print to test and verify my results. So in other words, each one of the varibles which contain data would be stored in a separate field (column) in the table.

Thanks

Ronnie

Nevyn SSChampion Points: 14210 More actions · Answer 10

Can you post the create table script for arttemp? Even better would be the scripts for each table in your procedure, and some sample data scripted as insert commands so people can test their solutions.

Its not that hard to rewrite this without a cursor if all you want is results into a table. But I need to know a bit more about arttemp to avoid making assumptions. And if you post all the scripts and some data I can test a solution before posting it.

SQLfan75 SSC Enthusiast Points: 160 More actions · Answer 11

I haven't seen anyone post code, so here's my first attempt:

select TOP (100)

/* your 4 fields */

grp.localGroupName0,

sys.Netbios_Name0,

wittSrv.server_id,

MemberName = case substring(grp.MemberName0,1,len(sys.netbios_name0))

when sys.Netbios_Name0 then '*local*' + substring(grp.MemberName0,len(sys.netbios_name0) + 1,len(grp.MemberName0))

else grp.MemberName0 end,

/* then this */

prt = wittSrv.server_id+' : '

+sys.Netbios_Name0+' : '

+case substring(grp.MemberName0,1,len(sys.netbios_name0))

when sys.Netbios_Name0 then '*local*' + substring(grp.MemberName0,len(sys.netbios_name0) + 1,len(grp.MemberName0))

else grp.MemberName0 end

+case when art.ito_group is null then ' not found' else ' Found' end

/* or this */

,isFound = case when art.ito_group is null then 'not found' else 'Found' end

into #your_table

from v_gs_wshsms_localgroupmembers grp

join v_r_system_valid sys

on sys.resourceid = grp.resourceid

join [witt_info].dbo.servers_v wittSrv

on sys.netbios_name0 = wittSrv.server_name

left outer join (select distinct ito_group, mech_id, server_id

from sox_audit.dbo.arttemp) as art

on art.ito_group = grp.localGroupName0

and art.mech_id = case substring(grp.MemberName0,1,len(sys.netbios_name0))

when sys.Netbios_Name0 then '*local*' + substring(grp.MemberName0,len(sys.netbios_name0) + 1,len(grp.MemberName0))

else grp.MemberName0 end

and art.server_id = wittSrv.server_id

order by sys.netbios_name0, grp.localgroupname0, grp.membername0

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 12

Thank you very much. I ran the query and it worked perfectly. Of course I tweaked it a little (very little) but the logic work as expected.

I really appreciate your help as well as everyone that offered some input. It took only 6 sec to return data where it was longer using the cursor.

Thanks again!!

Ronnie

SQLfan75 SSC Enthusiast Points: 160 More actions · Answer 13

Great! I'm glad it worked out, that was my first post;-)