SQL WHILE CURSOR and Insert problem

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 WHILE CURSOR and Insert problem

Author

Topic

cocono1
Starting Member

7 Posts

Posted - 2011-09-16 : 15:33:12

Hi there,

I am new to this forum so apologies if this has been covered before.

I am trying to write a procedure that looks at records in one table and based on these write to another table.

For example I have a table TMSUSER.SCHEDPOS which has records

Empref AUTOKEY DAY1 POSITION TIME
8888 1 1 ABU001 0
8888 2 2 ABU007 840

I want insert into TMSUSER.TMSCLK records based on this table.
ie From a set date @@PERIODSTART add DAY1 to it and insert values.

The code I was trying is

declare @@uniquekey float
declare @@day1 float
declare @@empref varchar (20)
declare @@clkdt datetime
declare @@periodst datetime

set @@periodst='09/19/2011 00:00:00 AM'
set @@empref='8888'

DECLARE SchedPos CURSOR FOR
SELECT day1
FROM tmsuser.schedpos
WHERE day1<15 and empref=@@empref;
OPEN SchedPos;

FETCH NEXT FROM SchedPos;
WHILE @@FETCH_STATUS = 0
BEGIN
set @@uniquekey=((SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ))+ cast(@@empref as float))*100000000000
set @@uniquekey = @@uniquekey/1000
set @@uniquekey = round(@@uniquekey,0)

set @@clkdt = @@periodst + (select DAY1 from tmsuser.schedpos)

FETCH NEXT FROM SchedPos;
insert into tmsuser.tmsclk (empref,clkdt,uniquekey)
values
(@@empref,@@clkdt,@@uniquekey)

END;
CLOSE SchedPos;
DEALLOCATE SchedPos;
GO

The problem I am having is in this line
set @@clkdt = @@periodst + (select day1 from tmsuser.schedpos)

It is pulling both records from the table and giving me the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I set it to just look at that DAY1 record that it is looking at in the loop?

Thanks,

Conor

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 15:46:34

Rather than creating a cursor for such an insert, could you just do something like this?;

INSERT TMSUSER.TMSCLK (empref,clkdt,uniquekey)
SELECT 
	empref, 
	@@periodst + 1,
	((SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ))+ cast(@@empref as float))*100000000000
FROM tmsuser.schedpos
WHERE day1<15 and empref=@@empref

cocono1
Starting Member

7 Posts

Posted - 2011-09-16 : 15:54:35

Thanks EHORN but I don't always want to add an increment of 1 to @@PERIODST --> It is to be driven by the DAY1 field in SCHEDPOS.

So if DAY1 is 2 then add 2 to @@PERIODST

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 16:03:43

quote:
Originally posted by cocono1

Thanks EHORN but I don't always want to add an increment of 1 to @@PERIODST --> It is to be driven by the DAY1 field in SCHEDPOS.

So if DAY1 is 2 then add 2 to @@PERIODST

I see. Could you just modify the inserted value for [clkdt] to the following?;


@@periodst + [day1]

cocono1
Starting Member

7 Posts

Posted - 2011-09-16 : 16:18:29

Essentially thats what I am trying to do but I get

Invalid column name 'day1'.

When I try and call it. I don't know how I call the value currently in the loop.

Thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-16 : 17:47:45

If you simply post a realistic set of sample data in tmsuser.schedpos and the desired results for tmsuser.tmsclk based on your sample data then I'm sure someone can post a simple set-based solution for you. Ideally post working DDL/DML to create/populate a sample table.

Be One with the Optimizer
TG

cocono1
Starting Member

7 Posts

Posted - 2011-09-16 : 18:52:38

Thanks TG - I hope this explains what I am looking for.

SCHEDPOS TABLE
Empref DAY1
8888 1
8888 2
8888 5

Expected out put to TMSCLK

8888, 09/20/2011 00:00:00 AM',123456
8888, 09/21/2011 00:00:00 AM',987654
8888, 09/24/2011 00:00:00 AM',246531

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 09:33:32

quote:
Originally posted by cocono1

Thanks TG - I hope this explains what I am looking for.

SCHEDPOS TABLE
Empref DAY1
8888 1
8888 2
8888 5

Expected out put to TMSCLK

8888, 09/20/2011 00:00:00 AM',123456
8888, 09/21/2011 00:00:00 AM',987654
8888, 09/24/2011 00:00:00 AM',246531

It appears to be an INSERT statement with a DATEADD function. Something like;

DECLARE @periodst smalldatetime; SET @periodst='09/19/2011 00:00:00 AM'

SELECT 
	Empref,
	DATEADD(dd,DAY1,@periodst)	 
FROM SCHEDPOS

It is not clear what your requirements are for the uniqueid though. Could you elaborate on your need for this field?

cocono1
Starting Member

7 Posts

Posted - 2011-09-17 : 13:04:28

Hi guys,

Thanks for you help. I got it resolved using a cursor. I didnt realize but my need to use the cursor is beacause there is a trigger on TMSCLK table. TMSCLK requires a unique ID and thats why the RAND number is generated (this wasn't causing the problem)

I was able to use the variables using the below code

"fetch next from curs_schedpos
into @@empref,@@day1,@@position1,@@time1"

And work from there.

declare @@uniquekey float
declare @@day1 float
declare @@empref varchar (20)
declare @@clkdt datetime
declare @@periodst datetime
declare @@position1 varchar (20)
declare @@time1 datetime
set @@clkdt='09/19/2011 00:00:00 AM'
set @@periodst='09/19/2011 00:00:00 AM'
set @@empref='8888'

declare curs_schedpos cursor for
select empref,day1,position1,time1 from tmsuser.schedpos order by empref,day1,time1

for update

open curs_schedpos

fetch next from curs_schedpos
into @@empref,@@day1,@@position1,@@time1

while @@fetch_status=0
begin
--SELECT RAND() AS [RandomNumber]
set @@uniquekey=((SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ))+ cast(@@empref as float))*100000000000
set @@uniquekey = @@uniquekey/1000 + @@day1 --+ @@time1
set @@uniquekey = round(@@uniquekey,0)

insert into tmsuser.tmsclk (empref,clkdt,uniquekey)
values
(@@empref,@@clkdt+@@day1,@@uniquekey)

fetch next from curs_schedpos
into @@empref,@@day1,@@position1,@@time1
end

close curs_schedpos
deallocate curs_schedpos

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-17 : 17:46:52

if you are pleased with your cursor solution, that's fine. But it looks like the problem can be solved by set base solution. Set base solution almost always has better performance than cursor. If you are concerned about performance, explain more about your requirement, people can help you out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources