Cursor Tuning Help!

This cursor is processing one row per second, all it is doing is appending a value to a column. There are 847,000 rows and I can't leave the query to run for 10 days! help!

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPEND
as

begin

DECLARE @.I_UPN varchar(13)
DECLARE @.I_SURNAME varchar(50)
DECLARE @.I_FORENAME varchar(50)
DECLARE @.I_DOB datetime
DECLARE @.I_GENDER varchar(1)
DECLARE @.I_LEA varchar(3)
DECLARE @.I_DFES varchar(4)
DECLARE @.I_ATTEND_YEAR varchar(4)
DECLARE @.I_WEEK_BEGINNING datetime
DECLARE @.I_ATTEND_CODES varchar(14)

declare @.cnt int

set nocount on

declare cur CURSOR

for select upn,
surname,
forename,
dob,
gender,
'353' as lea,
dfes,
attend_year,
week_beginning,
attend_codes
from tmpATTEND_IMPORT

for read only

open cur

fetch from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES

truncate table tmpATTEND_IMPORT_APPENDED

while @.@.fetch_status = 0
begin
set @.cnt = (select count(*) as cnt from tmpATTEND_IMPORT_APPENDED
where upn = @.I_UPN
and surname = @.I_SURNAME
and forename = @.I_FORENAME
and dob = @.I_DOB
and gender = @.I_GENDER
and lea = @.I_LEA
and dfes = @.I_DFES
and attend_year = @.I_ATTEND_YEAR)

if @.cnt = 0 insert tmpATTEND_IMPORT_APPENDED(
upn,
surname,
forename,
dob,
gender,
lea,
dfes,
attend_year,
week_beginning,
attend_codes)
values (
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES)

else update tmpATTEND_IMPORT_APPENDED
set attend_codes = attend_codes+@.I_ATTEND_CODES
where upn = @.I_UPN and
surname = @.I_SURNAME and
forename = @.I_FORENAME and
dob = @.I_DOB and
gender = @.I_GENDER and
lea = @.I_LEA and
dfes = @.I_DFES and
attend_year = @.I_ATTEND_YEAR

fetch next from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES

end

close cur

deallocate cur

end
GO

I tried adapting a post resolved on Romanian SQL Server User Group (www.sqlserver.ro) , to concatenate the columns

DECLARE @.MyTable TABLE (ID INT PRIMARY KEY, VAL VARCHAR(64))

INSERT INTO @.MyTable VALUES (1, 'Cristian')

INSERT INTO @.MyTable VALUES (2, 'Sorin')

INSERT INTO @.MyTable VALUES (3, 'Narcis')

DECLARE @.Lista VARCHAR(MAX)

SET @.Lista = '';

SELECT @.Lista=@.Lista + VAL + ' ' FROM @.MyTable

SELECT RTRIM(@.Lista) AS Lista

GO

-- Output

Lista

Cristian Sorin Narcis

so try the following procedure :

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPEND
as

begin

DECLARE @.I_UPN varchar(13)
DECLARE @.I_SURNAME varchar(50)
DECLARE @.I_FORENAME varchar(50)
DECLARE @.I_DOB datetime
DECLARE @.I_GENDER varchar(1)
DECLARE @.I_LEA varchar(3)
DECLARE @.I_DFES varchar(4)
DECLARE @.I_ATTEND_YEAR varchar(4)
DECLARE @.I_WEEK_BEGINNING datetime
DECLARE @.I_ATTEND_CODES varchar(14)

set nocount on

declare cur CURSOR

//select only distinct rows

for select upn,
surname,
forename,
dob,
gender,
'353' as lea,
dfes,
attend_year,
week_beginning,
from tmpATTEND_IMPORT
group by upn,surname,forename,dob,gender,lea,dfes,attend_year

for read only

open cur

fetch from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,

truncate table tmpATTEND_IMPORT_APPENDED

while @.@.fetch_status = 0
begin
//adding to @.I_ATTEND_CODES only rows that respect where clause
select @.I_ATTEND_CODES=attend_codes+@.I_ATTEND_CODES
from tmpATTEND_IMPORT
where upn = @.I_UPN and
surname = @.I_SURNAME and
forename = @.I_FORENAME and
dob = @.I_DOB and
gender = @.I_GENDER and
lea = @.I_LEA and
dfes = @.I_DFES and
attend_year = @.I_ATTEND_YEAR

// and insert what it find and add

insert tmpATTEND_IMPORT_APPENDED(
upn,
surname,
forename,
dob,
gender,
lea,
dfes,
attend_year,
week_beginning,
attend_codes)
values (
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,
@.I_ATTEND_CODES)

fetch next from cur into
@.I_UPN,
@.I_SURNAME,
@.I_FORENAME,
@.I_DOB,
@.I_GENDER,
@.I_LEA,
@.I_DFES,
@.I_ATTEND_YEAR,
@.I_WEEK_BEGINNING,

end

close cur