Cursor Query - 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

Cursor Query

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-12-21 : 13:20:48

Hi,

I have a requirement wherein I need to use cursor to update destination table. The reason I need to use cursor is we want to have row level insertion happen in the dest table.

The query that I am using does gives me error. Please let me know the correct syntax to use cursor to update the table:

DECLARE cur CURSOR FOR
BEGIN
UPDATE FRU.dbo.Contact
SET
contact_Updated_ind = 'Y'
, datetime_updated = getdate()
, last_action_cd = 'U'
, last_program = 'Fru_Loop'
, last_source_system = 'StoreComplaince'
, user_updated = USER
FROM
FRU.dbo.Contact C
INNER JOIN
Fru_Address WA
ON WA.Contact_ID = C.Contact_ID
INNER JOIN
CIF.dbo.Address_Type A
ON WA.Contact_ID = A.Contact_ID
AND WA.Address_Type = A.Address_Type
WHERE
WA.Contact_ID IS NOT NULL
AND
WA.Address_ID IS NOT NULL
AND
WA.Address_Type_ID IS NULL
OPEN cur
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
END
CLOSE cur
DEALLOCATE cur

Please advice.

Dp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-21 : 14:25:58

quote:
I have a requirement wherein I need to use cursor to update destination table. The reason I need to use cursor is we want to have row level insertion happen in the dest table

This makes absolutely NO sense. What is a "row-level insertion"? All INSERT operations in SQL Server insert rows. And are you updating the table or inserting new rows? You'll have to provide more details (table structure, sample data).

Also, the UPDATE statement you've written will perform an UPDATE without the need for a cursor at all (a cursor will only slow it down). If you need UPDATE rows that match, and INSERT rows that do not match, you can use individual INSERT and UPDATE statements in a transaction, or the MERGE statement if you're using SQL Server 2008. Again, we'll need more specific details before we can help.

dim
Yak Posting Veteran

57 Posts

Posted - 2010-12-21 : 15:10:31

Hi robvolk,

Below is the re-worked version of the Cursor Query :

DECLARE @ContactId int,
@address_id int,
@Address_Type varchar(50)
DECLARE cur CURSOR FOR
Select fru_address.contact_id,
fru_address.address_id,
fru_address.address_Type
from
fru_address
OPEN cur
FETCH NEXT FROM cur into @ContactId,@address_id,@Address_Type
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE FRU.dbo.Contact
SET
contact_Updated_ind = 'Y'
, datetime_updated = getdate()
, last_action_cd = 'U'
, last_program = 'Fruloop'
, last_source_system = 'StoreCompliance'
, user_updated = USER
FROM
FRU.dbo.Contact C
INNER JOIN
fru_address WA
ON WA.Contact_ID = C.Contact_ID
INNER JOIN
FRU.dbo.Address_Type A
ON WA.Contact_ID = A.Contact_ID
AND WA.Address_Type = A.Address_Type
WHERE
@ContactId IS NOT NULL
AND @address_id IS NOT NULL
AND @Address_Type IS NOT NULL
-- WA.Contact_ID IS NOT NULL
-- AND
-- WA.Address_ID IS NOT NULL
-- AND
-- WA.Address_Type_ID IS NULL
FETCH NEXT FROM cur into @ContactId,@address_id,@Address_Type
END CLOSE cur
DEALLOCATE cur

Based on the contact_id and address_type in the staging table this particular cursor query will just update the contact_Updated_ind flag. I undestand that there is absolute no need to use cursor for this updating task, but my management wants to have this way. No Choice. I need to guidance as to whether the join condition that the destination table is trying to do will remain as it is while using cursor or it requires any changes. Please let me know on this.

Thank You,
dp

Dp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-21 : 16:13:27

You probably need to change the WHERE clause like so:

WHERE WA.Contact_ID=@ContactId 
AND WA.Address_ID=@address_id 
AND WA.Address_Type=@Address_Type

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-21 : 16:31:02

quote:
Originally posted by robvolk

You probably need to change the WHERE clause like so:
WHERE WA.Contact_ID=@ContactId
AND WA.Address_ID=@address_id
AND WA.Address_Type=@Address_Type

I don't know...it's a pretty good mess to begin with, it MIGHT be what they want

dim (he he): Can you post, in Business terms (requirements) what you want to do?

Also, sample data and expected results would help

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/

dim
Yak Posting Veteran

57 Posts

Posted - 2010-12-21 : 17:58:14

Thank You Brett and robvolk,

After changing the code it worked : and I was able to verify that

DECLARE @ContactId int,
@address_id int,
@Address_Type varchar(50)
DECLARE cur CURSOR FOR
Select fru_address.contact_id,
fru_address.address_id,
fru_address.address_Type
from
fru_address
OPEN cur
FETCH NEXT FROM cur into @ContactId,@address_id,@Address_Type
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE FRU.dbo.Contact
SET
contact_Updated_ind = 'Y'
, datetime_updated = getdate()
, last_action_cd = 'U'
, last_program = 'Fruloop'
, last_source_system = 'StoreCompliance'
, user_updated = USER
FROM
FRU.dbo.Contact C
INNER JOIN
fru_address WA
ON WA.Contact_ID = C.Contact_ID
INNER JOIN
FRU.dbo.Address_Type A
ON WA.Contact_ID = A.Contact_ID
AND WA.Address_Type = A.Address_Type
WHERE
@ContactId IS NOT NULL
AND @address_id IS NOT NULL
AND @Address_Type IS NOT NULL
AND WA.Contact_ID=@ContactId
AND WA.Address_ID=@address_id
AND WA.Address_Type=@Address_Type
-- WA.Contact_ID IS NOT NULL
-- AND
-- WA.Address_ID IS NOT NULL
-- AND
-- WA.Address_Type_ID IS NULL
FETCH NEXT FROM cur into @ContactId,@address_id,@Address_Type
END CLOSE cur
DEALLOCATE cur

The piece I am working on was developed by somebody else and this was a last minute TR change :(.......I am still learning the Business. This system captures a contact information from the other source systems and then updates the information on itself if there is a match.

I also have another query : Can we issue commit transaction statement after every row is updated using the cursor. In that way we can come to know how many rows were written before the job failed . I tried with begin transaction and close transaction statements but was not able to successfully plant it on the cursor.

Please let me kow if there is a way to commit every update that happens through now using cursor.

Thank you

Dp

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-21 : 18:59:43

can you tell me what the difference between what you have and this?



UPDATE c
	   SET	contact_Updated_ind			= 'Y'
		  , datetime_updated			= getdate()
		  , last_action_cd				= 'U'
		  , last_program				= 'Fruloop'
		  , last_source_system			= 'StoreCompliance'
		  , user_updated				= USER()
	  FROM	FRU.dbo.Contact C
INNER JOIN  fru_address WA
		ON  WA.Contact_ID = C.Contact_ID
INNER JOIN  FRU.dbo.Address_Type A
		ON  WA.Contact_ID = A.Contact_ID
	   AND  WA.Address_Type = A.Address_Type

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/

dim
Yak Posting Veteran

57 Posts

Posted - 2010-12-22 : 09:33:45

Hi X002548,

There is no difference between the normal update statement you posted and the one with cursor that I have made. The reason that I am using cursor is beacuase of some error showing up when the SQL Agent job runs. The job keeps failing at the same step. The step at which it fails is the one which updates reacords on the dest table. With using cursor it will have row-level insertion and page level locks which might help me identify what kind of deadlock is occuring as the job keeps failing.

Dp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources