how to write a sql cursor ?

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

supergirl2008Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Your cursor statement is fine.

how many values does this return?

SELECT * FROM products_productincludes_mdup
0
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
matthew is correct, you do not need a cursor for this statement.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Patrick MatthewsCommented:
supergirl2008,

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

Regards,

Patrick
0
BrandonGalderisiCommented:
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
supergirl2008Author Commented:
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
supergirl2008Author Commented:
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
Patrick MatthewsCommented:
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
supergirl2008Author Commented:
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
Patrick MatthewsCommented:
supergirl2008,

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

:)

Regards,

Patrick
0
BrandonGalderisiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.