Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursor

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-18 : 13:48:47

This is my first time creating a cusor and I am not really sure how to do this.

What I have is a table that has history in it that I need to put into 1 line associated with the ID. When my cursor is finished. there are some notes, but the ID is not in the end result. Not sure what I am doing wrong.

DECLARE @UniqueHISTORYID nvarchar(1000), @FORMATTEDNOTES nvarchar(max),@counter int, @CurrentRecordNumber int
SET @CurrentRecordNumber = 0
SET @counter = (select COUNT(*) from GOLDMINE_HISTORY)
DECLARE cur cursor FAST_FORWARD FOR Select [UniqueHISTORYID],[FORMATTED NOTES] FROM GOLDMINE_HISTORY where[UniqueHISTORYID] <> ''
OPEN Cur
FETCH NEXT FROM Cur INTO @UniqueHISTORYID,@FORMATTEDNOTES
--I am assuming you created a table to store acc ID and notes...let's call it tnotes
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @formattednotes

--start of while loop
SET @CurrentRecordNumber = @CurrentRecordNumber + 1
PRINT ''
PRINT 'Processing Row ' + str(@currentrecordnumber) + ' of ' + str(@Counter)
IF (select COUNT(*) from tnotes where UniqueHISTORYID = @UniqueHISTORYID) > 0

UPDATE tnotes
SET [FORMATTED NOTES] = [FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~' --+ @Notes
Where UniqueHISTORYID = @UniqueHISTORYID and
[FORMATTED NOTES]= @FORMATTEDNOTES
ELSE
INSERT INTO tnotes ([UniqueHISTORYID], [FORMATTED NOTES]) VALUES (@UniqueHISTORYID, @FORMATTEDNOTES)
FETCH NEXT FROM cur INTO @UniqueHISTORYID, @FORMATTEDNOTES
END --end of while loop
PRINT @formattednotes

CLOSE cur
DEALLOCATE cur

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 14:03:38

how about you replace ALL of that, with


UPDATE tnotes
   SET [FORMATTED NOTES]   = [FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~'
  FROM tnotes t INNER JOIN GOLDMINE_HISTORY g
	ON t.UniqueHISTORYID   = g.UniqueHISTORYID 
   AND t.[FORMATTED NOTES] = g.[FORMATTEDNOTES]

INSERT INTO tnotes ([UniqueHISTORYID], [FORMATTED NOTES]) 
SELECT [UniqueHISTORYID],[FORMATTED NOTES] 
  FROM GOLDMINE_HISTORY o
 WHERE [UniqueHISTORYID] <> ''
   AND NOT EXISTS (SELECT * FROM GOLDMINE_HISTORY i
					WHERE i.[UniqueHISTORYID] = o.[UniqueHISTORYID])

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-18 : 14:52:50

Thank you Brett! Is there a way to put something in there that does like a return after a sentence? Right now it looks like it is all in one line.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 16:24:23

quote:
Originally posted by werhardt

Thank you Brett! Is there a way to put something in there that does like a return after a sentence? Right now it looks like it is all in one line.

If not following you?

I changed your cursor row by row based processing to a set based one

What do you mean a return?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 16:30:39

if i follow you, add this to what brett showed:

change this

SET [FORMATTED NOTES]   = [FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~'

to this:

SET [FORMATTED NOTES]   = [FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~' + char(10)

or maybe you want to change the '~~~' to char(10) ? not 100% sure what you're trying to do

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-20 : 13:00:52

Sorry I am just responding back. What I meant by the return was. If I had a note that was something like this below....

CC to discuss contract and access fee. CC went well.

Then the next note is this below. Is there a way instead of putting a ~~~ can we just make it enter down to the next line in the column?

We need to check on the insurance and Greg will check on removing the delegated contract, which includes the access fee ($50 x 600 = $30k) TOO MUCH!!!

Also, I have to add another column in like history, but it is called activity.

How can I get the history and the activity to both go into the tnotes without just only one pulling and then the other. I created this cursor, it works, but it doesn't let me put both in at the same time.

Thanks for you help!

DECLARE @UniqueHISTORYID nvarchar(1000), @FORMATTEDNOTES nvarchar(max),@Activity nvarchar(max),@counter int, @CurrentRecordNumber int
SET @CurrentRecordNumber = 0
SET @counter = (select COUNT(*) from GOLDMINE_HISTORY)
DECLARE cur cursor FAST_FORWARD FOR Select [UniqueHISTORYID],[FORMATTED NOTES],[Activity] from GOLDMINE_HISTORY --where [FORMATTED NOTES]<> ''
OPEN Cur
FETCH NEXT FROM Cur INTO @UniqueHISTORYID,@FORMATTEDNOTES,@Activity
--I am assuming you created a table to store acc ID and notes...let's call it tnotes
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @formattednotes

--start of while loop
SET @CurrentRecordNumber = @CurrentRecordNumber + 1
PRINT ''
PRINT 'Processing Row ' + str(@currentrecordnumber) + ' of ' + str(@Counter)
IF (select COUNT(*) from tnotes where UniqueHISTORYID = @UniqueHISTORYID) > 0

UPDATE tnotes
SET [FORMATTED NOTES] = [FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~' --+ @Notes
Where UniqueHISTORYID = @UniqueHISTORYID
ELSE

UPDATE tnotes
SET [Activity] = [Activity] + '~~~' + @Activity + '~~~' --+ @Notes
Where UniqueHISTORYID = @UniqueHISTORYID

INSERT INTO tnotes ([UniqueHISTORYID],[FORMATTED NOTES],[Activity]) VALUES (@UniqueHISTORYID, @FORMATTEDNOTES,@Activity)
FETCH NEXT FROM cur INTO @UniqueHISTORYID, @FORMATTEDNOTES,@Activity
END --end of while loop
PRINT @formattednotes

CLOSE cur
DEALLOCATE cur

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 13:31:05

You're just not getting the set based thing, are you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-20 : 13:33:06

I'm afraid not :(

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 14:04:53

what happened when u tried Brett's script? did you add the Char(10) ?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-20 : 14:13:06

Actually I did nt act the Char(10)Yet.

When I tried to use brett script it wasn't reconizing some of the tables see below in red....

It didn't recognize [FORMATTED NOTES] in the into statement and then the bottom

the o.[UniqueHISTORYID] was in red saying....multi part identifier.

DECLARE @UniqueHISTORYID nvarchar(1000), @FORMATTEDNOTES nvarchar(max),@Activity nvarchar(max),@counter int, @CurrentRecordNumber int
SET @CurrentRecordNumber = 0
SET @counter = (select COUNT(*) from GOLDMINE_HISTORY)
DECLARE cur cursor FAST_FORWARD FOR Select [UniqueHISTORYID],[FORMATTED NOTES],[Activity] from GOLDMINE_HISTORY --where [FORMATTED NOTES]<> ''
OPEN Cur
FETCH NEXT FROM Cur INTO @UniqueHISTORYID,@FORMATTEDNOTES,@Activity
--I am assuming you created a table to store acc ID and notes...let's call it tnotes
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @formattednotes

--start of while loop
SET @CurrentRecordNumber = @CurrentRecordNumber + 1
PRINT ''
PRINT 'Processing Row ' + str(@currentrecordnumber) + ' of ' + str(@Counter)
IF (select COUNT(*) from tnotes where UniqueHISTORYID = @UniqueHISTORYID) > 0

UPDATE tnotes
SET [FORMATTED NOTES] = g.[FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~'
FROM tnotes t INNER JOIN GOLDMINE_HISTORY g
ON t.UniqueHISTORYID = g.UniqueHISTORYID
AND t.[FORMATTED NOTES] = g.[FORMATTED NOTES]

INSERT INTO tnotes ([UniqueHISTORYID], [FORMATTED NOTES])
SELECT [UniqueHISTORYID],[FORMATTED NOTES],[ACTIVITY]
FROM GOLDMINE_HISTORY o
order by [UniqueHISTORYID]
WHERE [UniqueHISTORYID] <> ''
AND NOT EXISTS (SELECT * FROM GOLDMINE_HISTORY i
WHERE i.[UniqueHISTORYID] = o.[UniqueHISTORYID])

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 14:21:41

[FORMATTED NOTES] came from YOUR post not HIS. LOL. He used the information you provided.

Perhaps if you showed us the actual table definition we can get ya rolling.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 14:26:36

Post the DDL of the tables involved....

you gotta meet us at least 1/4 of the way

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-20 : 14:50:38

LOL....oh yeah I know [FORMATTED NOTES] is from my post. I just don't understand why it isn't working.

My actual table with the info in it is called, GOLDMINE_HISTORY. The columns in that table is [UniqueHISTORYID],[FORMATTED NOTES],[Activity]. What I need to do is have all the lines of [Activity] in one column and then [Formatted Notes]in one column. Does that make sense?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-11-24 : 11:36:08

How I am joining everything is, I am using the UniqueHISTORYID field. Then, after I have all of the data,
I need to have that join with the field UniqueMasterHISTORYID in the ALL_GOLDMINE_INFO_with_EMAIL That will
be [UniqueHISTORYID = [UniqueMasterHISTORYID].

Is this what you were looking for?

Here are the tables below........
Under that I put the cursor that I created.

This is the table that has all of the history and activity in it.

Table [GOLDMINE_HISTORY](
[UniqueHISTORYID] [nvarchar](1000) NULL,
[ACCOUNTNO] [nvarchar](20) NOT NULL,
[COMPANY] [nvarchar](40) NULL,
[CONTACT] [nvarchar](40) NULL,
[ACTIVITY] [nvarchar](max) NULL,
[FORMATTED NOTES] [nvarchar](max) NULL

-------------------------------------------------------------------------

This is the table that I am trying to put everything in. I want the activity to be on the same line as the History,
but in differenct columns.

[dbo].[tnotes](
[UniqueHISTORYID] [nvarchar](1000) NULL,
[FORMATTED NOTES] [nvarchar](max) NULL,
[Activity] [nvarchar](max) NULL

-------------------------------------------------------------------------------------------
Once that is finished I need to link this to my final tabled

[ALL_GOLDMINE_INFO_with_EMAIL](
[uniquecodeMASTER] [nvarchar](1000) NULL,
[UniqueMasterHISTORYID] [nvarchar](1000) NULL,
[COMPANY] [nvarchar](40) NULL,
[CONTACT] [nvarchar](40) NULL,
[ADDRESS1] [nvarchar](40) NULL,
[ADDRESS2] [nvarchar](40) NULL,
[ADDRESS3] [nvarchar](40) NULL,
[CITY] [nvarchar](30) NULL,
[STATE] [nvarchar](20) NULL,
[ZIP] [nvarchar](10) NULL,
[County] [nvarchar](20) NULL,
[PHONE1] [nvarchar](25) NULL,
[FAX] [nvarchar](25) NULL,
[EMAIL] [nvarchar](35) NULL,
[Tax ID] [nvarchar](20) NULL,
[ACCOUNTNO] [nvarchar](20) NOT NULL
-------------------------------------------------------------------------
cursor

DECLARE @UniqueHISTORYID nvarchar(1000), @FORMATTEDNOTES nvarchar(max),@Activity nvarchar(max),@counter int, @CurrentRecordNumber int
SET @CurrentRecordNumber = 0
SET @counter = (select COUNT(*) from GOLDMINE_HISTORY)
DECLARE cur cursor FAST_FORWARD FOR Select [UniqueHISTORYID],[FORMATTED NOTES],[Activity] from GOLDMINE_HISTORY --where [FORMATTED NOTES]<> ''
OPEN Cur
FETCH NEXT FROM Cur INTO @UniqueHISTORYID,@FORMATTEDNOTES,@Activity
--I am assuming you created a table to store acc ID and notes...let's call it tnotes
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @formattednotes

--start of while loop
SET @CurrentRecordNumber = @CurrentRecordNumber + 1
PRINT ''
PRINT 'Processing Row ' + str(@currentrecordnumber) + ' of ' + str(@Counter)
IF (select COUNT(*) from tnotes where UniqueHISTORYID = @UniqueHISTORYID) > 0

UPDATE tnotes
SET [FORMATTED NOTES] = t.[FORMATTED NOTES] + '~~~' + @FORMATTEDNOTES + '~~~' --+ @Notes
From tnotes t INNER JOIN GOLDMINE_HISTORY g
ON t.UniqueHISTORYID = g.UniqueHISTORYID and
t.[FORMATTED NOTES] = g.[FORMATTED NOTES]

--Where UniqueHISTORYID = @UniqueHISTORYID
--ELSE

UPDATE tnotes
SET [Activity] = t.[Activity] + '~~~' + @Activity + '~~~' --+ @Notes
From tnotes t INNER JOIN GOLDMINE_HISTORY g
ON t.UniqueHISTORYID = g.UniqueHISTORYID and
t.[Activity] = g.[Activity]

INSERT INTO tnotes ([UniqueHISTORYID],[FORMATTED NOTES],[Activity]) VALUES (@UniqueHISTORYID, @FORMATTEDNOTES,@Activity)
FETCH NEXT FROM cur INTO @UniqueHISTORYID, @FORMATTEDNOTES,@Activity
END --end of while loop
PRINT @formattednotes

CLOSE cur
DEALLOCATE cur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources