Solved

how to write a sql cursor ?

Posted on 2008-10-17
11
3,215 Views
Last Modified: 2012-06-27
i have the sql below. it seems to work fine but when i  add my insert so that for each record in products_productincludes_mdup i want to insert a few values from the current row int he cursor to a separate table.

the insert inserts the same line over and over in a infinite loop.

what is wrong in my code?

declare @ctz int, @ChildProductID int, @ParentProductID int, @IncludeStatus int 

	

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT * FROM products_productincludes_mdup

 

OPEN c1

 

FETCH NEXT FROM c1

INTO @ctz,@ParentProductID,@ChildProductID,@IncludeStatus

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

	

	insert into products_productincludes_mdup_singles (ParentProductID,ChildProductID,IncludeStatus)

	values (@ParentProductID,@ChildProductID,@IncludeStatus)

	

	print @ParentProductID

 

	FETCH NEXT FROM c1

	INTO @ctz,@ParentProductID,@ChildProductID,@IncludeStatus

 

END

 

CLOSE c1

DEALLOCATE c1

Open in new window

0
Comment
Question by:supergirl2008
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
Comment Utility
Your cursor statement is fine.

how many values does this return?

SELECT * FROM products_productincludes_mdup
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 300 total points
Comment Utility
Why do you need a cursor for this?

Have you tried:

insert into products_productincludes_mdup_singles (ParentProductID,ChildProductID,IncludeStatus)
SELECT ParentProductID,ChildProductID,IncludeStatus FROM products_productincludes_mdup
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
matthew is correct, you do not need a cursor for this statement.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
supergirl2008,

Basic rule of thumb: don't use a cursor unless there is no other way :)

Regards,

Patrick
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Basic rule of thumb: don't use a cursor.

If you need to loop over a data set, use a while loop.  But most things don't need it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:supergirl2008
Comment Utility
well what i am using it for is to remove the dup records of a table.


 basically i want to remove dups in a table.

i have a table called productincludes
it has a few fields listed here:
ParentProductID
ChildProductID
SummarizedRowID
IncludeStatus
LastUpdateDate
DisplayOrderByID


i ran a update for a business request that made many dup rows and want to clean this up. when i run this select to check on how many dups to verify cleanup:

select count(*) ctz,ParentProductID,
ChildProductID, IncludeStatus  from products_productincludes
where IncludeStatus  =0
group by ParentProductID,
ChildProductID, IncludeStatus  
order by ctz desc

notice i am grouping by only 3 of the rows not all the rows in the table !!!

i want only the rows that dup cause of these 3 rows to go away. the rest that are outside the groub by clause should stay. that is to say there can be more dups in the table as long as i dont have any dups on these 3 fields. thats what i want to achieve here.

thank you for the help
0
 

Author Comment

by:supergirl2008
Comment Utility
to do this i was thinking to create an aggregate table of dups with a group by (see above) then to do a select distinct from this tae into another one to get all the single records.

delete them from the original table abd then to cursor the single records back into the master table.

WOW. there has got to be a better way to get rid of dups. when i hear my self explain the process its way to big for a small task like this.


please lend me some expert advice !!!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
supergirl2008,

Then something like this should work:


INSERT DestinationTable (ColX, ColY, ColZ)
SELECT ColX, ColY, ColZ
FROM SourceTable
GROUP BY ColX, ColY, ColZ

or...

INSERT DestinationTable (ColX, ColY, ColZ)
SELECT DISTINCT ColX, ColY, ColZ
FROM SourceTable

Regards,

Patrick
0
 

Author Comment

by:supergirl2008
Comment Utility
actualt i found in SQL 2005 new features such as these are the best solution...

WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY ParentProductID,ChildProductID, IncludeStatus  ORDER BY ParentProductID,ChildProductID, IncludeStatus )) AS RNum FROM products_productincludes2)
DELETE FROM T1 WHERE RNum IN (SELECT a.RNum FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)

0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
supergirl2008,

If what you wanted was a DELETE, it would have been helpful if you had stated that in your question...

:)

Regards,

Patrick
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
http:#22743716 is a good approach to take as long as the table isn't being referenced by any foreign keys.

Chapmandew did a nice little writeup on handling this exact situation.
http://blogs.techrepublic.com.com/datacenter/?p=420
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now