Need to create a Cursor Example

[lsmyth1717]

I have been asked to produce an example of a cursor and then to point out why it isn't required to use on. I am having difficulty creating my example and I was wondering if someone could help me with the syntax to write one.

Basically I want to select the FavouriteSport table (below) into a cursor and then loop through each row and insert all records which have a value football in column sport1, sport2, and sport3 into the FavouriteSportMain table.

I know this is a strange request and you wouldn't need to use a cursor for this but this is what I need to demonstrate. Could someone please show me how to write the cursor code for this.

Cheers

Script to create table.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FavouriteSport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FavouriteSport]
GO

CREATE TABLE [dbo].[FavouriteSport] (
[URN] [bigint] IDENTITY (1, 1) NOT NULL ,
[Sport1] [char] (30) COLLATE Latin1_General_CI_AS NULL ,
[Sport2] [char] (30) COLLATE Latin1_General_CI_AS NULL ,
[Sport3] [char] (30) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FavouriteSportMain]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FavouriteSportMain]
GO

CREATE TABLE [dbo].[FavouriteSportMain] (
[URN] [bigint] IDENTITY (1, 1) NOT NULL ,
[Sport1] [char] (30) COLLATE Latin1_General_CI_AS NULL ,
[Sport2] [char] (30) COLLATE Latin1_General_CI_AS NULL ,
[Sport3] [char] (30) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
So if my FavouriteSport table looked like this:
URN Sport1 Sport2 Sport3
1 golf tennis gaelic
2 hurling football snooker
3 running rugby golf
4 Karate boxing football
5 Pool running gaelic

After the cursor had finished i'd want it to have inserted two rows
URN Sport1 Sport2 Sport3
2 hurling football snooker
4 Karate boxing football

 

[SajidAttar]

Remove the Identity Attribute of URN Column from FavouriteSportMain table. and execute the following code

declare @URN int, @Sport1 varchar(30), @Sport2 varchar(30), @Sport3 varchar(30), @mySport varchar(30)

declare curSport cursor for
select * from FavouriteSport


set @mySport = 'football'


open curSport
fetch next from curSport into @URN, @Sport1 , @Sport2, @Sport3
while @@fetch_status = 0
begin


  if (@Sport1 = @mySport) or (@Sport2 = @mySport) or (@Sport3 = @mySport)
	insert into FavouriteSportMain values (@urn, @Sport1, @Sport2, @Sport3)

fetch next from curSport into @URN, @Sport1 , @Sport2, @Sport3
end

close curSport
deallocate curSport

if you have to use identity for above column, use 'Set Identity_Insert'.

hope this helps you . .


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[lsmyth1717]

Thats great cheers.

Wondered if you could show me one more thing. How do I change it slightly so as if a row with the same details already exists in FavouriteSportMain then don't insert it.

Basically what I mean is if someone runs the cursor query a second time which inserts another 3 rows and I end up with six rows in the table 3 of which are duplicates. On the second run i'd like the cursor query to ignore the rows if they already exist.

Can you help me to do this.

Thanks again

 

[SajidAttar]

Replace following code with if statement

if not exist (select * from FavouriteSportMain where URN = @URN)
begin
if (@Sport1 = @mySport) or (@Sport2 = @mySport) or (@Sport3 = @mySport)
    insert into FavouriteSportMain values (@urn, @Sport1, @Sport2, @Sport3)

end

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[SajidAttar]

correction

if (@Sport1 = @mySport) or (@Sport2 = @mySport) or (@Sport3 = @mySport)
begin
if not exist (select * from FavouriteSportMain where URN = @URN)
    insert into FavouriteSportMain values (@urn, @Sport1, @Sport2, @Sport3)

end

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[lsmyth1717]

I changed my query as follows and got the following error: -

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.

Any ideas.

Also is it possible to do the if not exists based on the value of all 4 columns and not just the URN. I know this again is a strange request but would like to show this if possible

declare @URN int, @Sport1 varchar(30), @Sport2 varchar(30), @Sport3 varchar(30), @mySport varchar(30)

declare curSport1 cursor for
select * from FavouriteSport


set @mySport = 'football'


open curSport1
fetch next from curSport1 into @URN, @Sport1 , @Sport2, @Sport3
while @@fetch_status = 0
begin

if (@Sport1 = @mySport) or (@Sport2 = @mySport) or (@Sport3 = @mySport)
begin
if not exist (select URN from FavouriteSportMain where URN = @URN)
insert into FavouriteSportMain values (@urn, @Sport1, @Sport2, @Sport3)
end

fetch next from curSport1 into @URN, @Sport1 , @Sport2, @Sport3
end

close curSport1
deallocate curSport1

 

[SajidAttar]

Sorry Its my mistake (I have written [red]EXIST[/red] instead of [blue] EXISTS [/blue]).

replace if statement with new one

if not exists (select URN from FavouriteSportMain where URN = @URN )

you can add filter on value of all 4 columns

if not exists (select * from FavouriteSportMain where URN = @URN and Sport1 = @Sport1 and Sport2 = @Sport2 and Sport3 = @Sport3)

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell