Solved

how to write a sql cursor ?

Posted on 2008-10-17
11
3,221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 50
SQL Throw Error 7 35
Need SSIS project 2 30
Using datetime as triggers 2 23
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

733 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