Using a cursor - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using a cursor

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-08 : 13:28:41

This stored procedure is working but it takes 30 mins to run. Can you see if I have this set up properly. Thanks.

set NOCOUNT ON
declare @alldiariesid int

declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

declare @SSIDiariesID int, @area varchar(2), @dist varchar(3), @doc varchar(3), @hun varchar(9),
@recno varchar(2), @pan varchar(9), @fname varchar(15), @lname varchar(20),
@drycd1 varchar(2), @drydte1 varchar(8), @dryfu1 varchar(1),
@drycd2 varchar(2), @drydte2 varchar(8), @dryfu2 varchar(1),
@drycd3 varchar(2), @drydte3 varchar(8), @dryfu3 varchar(1),
@curstat varchar(3)

Declare dry_cursor CURSOR FOR
select SSIDiariesID, area, dist, doc, hun,
recno, pan, fname, lname,
drycd1, drydte1, dryfu1,
drycd2, drydte2, dryfu2,
drycd3, drydte3, dryfu3,
curstat
FROM SSIDiaries

Open dry_cursor
Fetch next from dry_cursor INTO
@SSIDiariesID, @area, @dist, @doc, @hun,
@recno, @pan, @fname, @lname,
@drycd1, @drydte1, @dryfu1,
@drycd2, @drydte2, @dryfu2,
@drycd3, @drydte3, @dryfu3,
@curstat

WHILE (@@FETCH_STATUS=0)
BEGIN
insert into WeeklyDiariesFile
(weeklyID, area, dist, doc, pan, fname, lname, week_start_date)
select @SSIDiariesID as weeklyID,
@area, @dist, @doc,
@pan, @fname, @lname, convert(nvarchar,@dte,101)

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @SSIDiariesID as weeklyid, @hun, @recno, @drycd1 as drycde,
@drydte1 as drydat, @dryfu1 as dryfu, @curstat,
convert(nvarchar,@dte,101)

IF @drycd2 !=' '
insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @SSIDiariesID as weeklyid,
@hun, @recno, @drycd2 as drycde,
@drydte2 as drydat, @dryfu2 as dryfu, @curstat,
convert(nvarchar,@dte,101)

IF @drycd3 !=' '
insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select @ssidiariesid as weeklyid,
@hun, @recno, @drycd3 as drycde,
@drydte3 as drydat, @dryfu3 as dryfu, @curstat,
convert(nvarchar,@dte,101)

Fetch next from dry_cursor INTO
@SSIDiariesID, @area, @dist, @doc, @hun,
@recno, @pan, @fname, @lname,
@drycd1, @drydte1, @dryfu1,
@drycd2, @drydte2, @dryfu2,
@drycd3, @drydte3, @dryfu3,
@curstat
END

CLOSE dry_cursor
DEALLOCATE dry_cursor

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 13:40:54

I don't see a cause to make this inserts via cursor.
Cursor is very slow!

You can make these inserts in a similar form using WHERE-clauses.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-08 : 13:44:00

my thoughts exactly! Something like this?


declare @alldiariesid int
declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)


insert into WeeklyDiariesFile
(weeklyID, area, dist, doc, pan, fname, lname, week_start_date)
select SSIDiariesID as weeklyID,
area, dist, doc,
pan, fname, lname, convert(nvarchar,@dte,101)
FROM SSIDiaries

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select SSIDiariesID as weeklyid, hun, recno, drycd1 as drycde,
drydte1 as drydat, dryfu1 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries


insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select SSIDiariesID as weeklyid,
hun, recno, drycd2 as drycde,
drydte2 as drydat, dryfu2 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd2 !=' '

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select ssidiariesid as weeklyid,
hun, recno, drycd3 as drycde,
drydte3 as drydat, dryfu3 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd3 !=' '

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:49:28

[code]declare @alldiariesid int
declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)

insert into WeeklyDiariesFile
(weeklyID, area, dist, doc, pan, fname, lname, week_start_date)
select SSIDiariesID as weeklyID,
area, dist, doc,
pan, fname, lname, convert(nvarchar,@dte,101)
FROM SSIDiaries

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select SSIDiariesID as weeklyid, hun, recno, drycd1 as drycde,
drydte1 as drydat, dryfu1 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries

union all

select SSIDiariesID as weeklyid,
hun, recno, drycd2 as drycde,
drydte2 as drydat, dryfu2 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd2 !=' '

union all

select ssidiariesid as weeklyid,
hun, recno, drycd3 as drycde,
drydte3 as drydat, dryfu3 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd3 !=' '

[/code]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-08 : 13:58:24

Thanks TG and Visakh16 I tried both of your queries and got this:

Cannot insert explicit value for identity column in table 'WeeklyDiaries' when IDENTITY_INSERT is set to OFF.

Records are going into AllDiaries.

Here's my table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WeeklyDiaries](
[WeeklyID] [int] IDENTITY(1,1) NOT NULL,
[area] [varchar](3) NULL,
[dist] [varchar](3) NULL,
[doc] [varchar](3) NULL,
[pan] [varchar](9) NULL,
[fname] [varchar](15) NULL,
[lname] [varchar](20) NULL,
[week_start_date] [nvarchar](30) NULL,
CONSTRAINT [PK_WeeklyDiariesTest] PRIMARY KEY CLUSTERED
(
[WeeklyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 14:03:16

the message suggested what to do!


SET IDENTITY_INSERT WeeklyDiariesFile ON 
declare @alldiariesid int
declare @dte datetime --to convert the date
set @dte=offices.dbo.endofweek(getdate()- 7)


insert into WeeklyDiariesFile
(weeklyID, area, dist, doc, pan, fname, lname, week_start_date)
select SSIDiariesID as weeklyID,
area, dist, doc,
pan, fname, lname, convert(nvarchar,@dte,101)
FROM SSIDiaries

SET IDENTITY_INSERT WeeklyDiariesFile OFF

insert into AllDiaries
(weeklyid, hun, recno, drycde, drydat, dryfu, curstat, week_start_date)
select SSIDiariesID as weeklyid, hun, recno, drycd1 as drycde,
drydte1 as drydat, dryfu1 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries

union all

select SSIDiariesID as weeklyid,
hun, recno, drycd2 as drycde,
drydte2 as drydat, dryfu2 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd2 !=' '

union all

select ssidiariesid as weeklyid,
hun, recno, drycd3 as drycde,
drydte3 as drydat, dryfu3 as dryfu, curstat,
convert(nvarchar,@dte,101)
from FROM SSIDiaries
where drycd3 !=' '

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 14:04:00

In your first post is no table named WeeklyDiaries!
You have also posted that your cursor RUNS but too slow!
So what is the real thing?

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-08 : 14:09:05

It was mentioned in the first post right under the FETCH_STATUS section...

WHILE (@@FETCH_STATUS=0)
BEGIN
insert into WeeklyDiariesFile
(weeklyID, area, dist, doc, pan, fname, lname, week_start_date)
select @SSIDiariesID as weeklyID,
@area, @dist, @doc,
@pan, @fname, @lname, convert(nvarchar,@dte,101)

I guess I should have put spaces in between some of the lines so you could see it.

The code I first posted does work but it takes 30 mins to complete. Want to speed up the query if that's possible

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 14:15:54

Maybe I am blind...
WeeklyDiaries / WeeklyDiariesFile