Solved

how to write a sql cursor ?

Posted on 2008-10-17
11
3,216 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
ID: 22742981
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
ID: 22742984
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
ID: 22742996
matthew is correct, you do not need a cursor for this statement.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22743106
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
ID: 22743169
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:supergirl2008
ID: 22743508
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
ID: 22743531
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
ID: 22743578
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
ID: 22743716
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
ID: 22743756
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
ID: 22743823
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

21 Experts available now in Live!

Get 1:1 Help Now