SQL Cursor help - 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

SQL Cursor help

Author

Topic

HH7007
Starting Member

5 Posts

Posted - 2010-11-23 : 22:57:13

I am writing a cursor that inserts records into table X. The data that is being processed is huge and in the magnitude of 5 Million records.
I added a line Print 'Processing ID: '+@x with in the cursor hoping to see the progress as the cursor completes each record. Apparently, SQL displays these msgs at once after processing the entire cursor. Well, that defeats the purpose of adding the Print cmd inside the cursor. Is this normal? Is there a way to print a line in sql query result window as the cursor processes each record? Thanks in Advance!!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-11-24 : 02:09:39

print for every row when there are millions of records?
Why do you want to use a cursor to insert rows?

HH7007
Starting Member

5 Posts

Posted - 2010-11-24 : 10:13:14

The intent is not to print for every row that is inserted. The intent is to print the ID that it is used to insert the child records. Each ID will approximately insert 50,000 records into a table and I would like to print which ID is being processed as the cursor fetches the next record.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 10:15:53

post the cursor and the table DDL

By printing out this message, you might just blow out the buffer/memory

ever hear of @@ROWCOUNT???

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HH7007
Starting Member

5 Posts

Posted - 2010-11-24 : 10:50:20

Thanks for responding..below are the details

Table Customer: Has 50K records (columns: CustomerID)
Table Products: Has 25K records (columns: ProductID)
Table CustPROD: Will hold product details for each customer (columns: CustomerID, ProductID)

Cursor will insert records into CUSTPROD table. It is as good as a cross join.
I would be more comfortable using a cross join, unfortunately due to other process dependences and filters while fetching custid and items not shown in this example, cannot eliminate cursor.

DECLARE xCURZ Cursor for
Fetch next from xCURZ into @xCUSTID

While @@fetch_status = 0
Begin
Print 'Processing: '+@xCUSTID
Insert into CUSTPROD
select CUSTomerID, ProductID from Customer Cross join Product WHERE CustomerID = @xCUSTID
FETCH NEXT FROM xCURZ INTO @xCUSTID
END
Close xCURZ
DEALLOCATE xCURZ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 10:54:23

i dont think you need a cursor for this at least by seeing the usage above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 11:12:17

I don't even see the cursor definition

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HH7007
Starting Member

5 Posts

Posted - 2010-11-24 : 12:03:46

Brett thanks again for the response.. I'm sorry I did not understand what you mean by "cursor definition"
What exactly are you looking for. An explanation of what the cursor is suppose to do?

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 12:07:14

[code]
DECLARE xCURZ Cursor for
?????????????????????????????????????????????????????? for WHAT?

Fetch next from xCURZ into @xCUSTID

While @@fetch_status = 0
Begin
Print 'Processing: '+@xCUSTID
Insert into CUSTPROD
select CUSTomerID, ProductID from Customer Cross join Product WHERE CustomerID = @xCUSTID
FETCH NEXT FROM xCURZ INTO @xCUSTID
END
Close xCURZ
DEALLOCATE xCURZ
[/code]

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HH7007
Starting Member

5 Posts

Posted - 2010-11-24 : 12:13:55

Opps.. sorry, did not realize.
It is "Select CustomerID from V_CUSTFILTER"
V_CUSTFILTER is a SQL view with filters.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 12:22:27

How about



INSERT INTO CUSTPROD (CUSTomerID, ProductID) 
     SELECT c.CUSTomerID, c.ProductID 
       FROM (Customer c
			  INNER JOIN  V_CUSTFILTER
					  ON c.CustomerID = v.CustomerID) AS XXX 
 CROSS JOIN Product

However, I am at a loss to understand WHY you are doing this...

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources