Cursor / Transact-SQL Question
Jul 1, 1999Hi all,
I need to run a long process in a cursor. I'd like to perform a special action every 10,000th row. How would you implement that using Transact-SQL?
Many thanks,
-Kevin Kline
ADVERTISEMENT
Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor
Aug 12, 2015 In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
Transact SQL :: Alternative For Cursor
Oct 28, 2015I have a Stored Procedure. In that SP, I am calling 10 table-valued user defined function to calculate different pricing charges (lets say delivery charge, fuel surcharge, etc). In that SP, there is one Temporary table to store the data required for calculating charges and some columns will hold the calculated data in the same temp table.
1. First step is loading the columns in Temporary table in the SP which are required for calculating charges (will be having 1000 records for example)
2. Second step is to calculate charges ( delivery charge, fuel surcharge, etc). for each rows(1000 rows) from the temporary table using table-valued user defined functions one by one*Call function to calculate DeliveryCharge in the SP and calculate DeliveryCharge for all 1000rows. For this step i am using cursor to loop through 1000records and find DeliveryCharge for each row and update it in the DeliveryCharge column in the same temporary table. I am using 10 cursors for 10 different price calculations in the SP
3. Finally, the SP will return that 1000 records with calculated prices. The question is how to avoid Cursor for these operations. How to pass all 1000 records to a function and get table valued results from that function and update those results in the Temporary table without using cursors?
Transact SQL :: Proper Use Of Cursor?
Jun 16, 2015Am I using the cursor feature properly here? -- of course there would be actual processing (replacing a while loop) going on and not simple print statements.
Declare @BadgeNumber varchar(20), @name varchar(100), @phone int, @status varchar(25)
Declare cursor Cursor For
Select jt.BadgeNumber, tj.Name, jt.Phone, zt.status
From employees jt
join employeestatus zt
On jt.id = zt.id
[code]....
Transact SQL :: Update Statement With Cursor
Jun 16, 2015When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
Transact SQL :: Cursor Fetch From Bottom To Top?
Sep 14, 2015I write few lines to do a bottom-up calculation, with 'fetch last' and 'fetch prior'.
It seems that the condition 'WHILE @@FETCH_STATUS = 0' does not work when cursor arrives at the first line, as there is an error message:
'(1 row(s) affected)
6255.84
(1 row(s) affected)
Msg 16931, Level 16, State 1, Line 18
There are no rows in the current fetch buffer.
The statement has been terminated.'
how to fix the error?
Here is my code:
DECLARE @lastprice real
DECLARE @updatedprice real
DECLARE @updatedRe real
DECLARE @updatedAUX real
SET @lastprice = (
SELECT Close_P from #ClosePrice where #ClosePrice.DateTD = (SELECT MAX(#ClosePrice.DateTD) FROM #ClosePrice)
)
[code].....
Transact SQL :: How To Write A Cursor For Row Count
May 20, 2015I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
Transact SQL :: Standard And Correct Cursor Format
Aug 12, 2015I have a table which is a configuration table, I have declared cursors whereby the cursor doesn't get to all the rows in the configuration table even though there is no where clause in the select statement and the cursor ought to loop/go through every single row. Changed the cursor to the below and it started to go through all rows.
DECLARE XXX CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING FOR
Now with the definition above, I am now having a situation whereby a column in row 1 which is a bit data type and has a value of 0, then the cursor gets to row 2 the same column but with a value of 1 and an if statement in the cursor saying
IF @row2columnX = 0 set @myval = 99
For some reason within the cursor the IF statement is being implemented even though the value in row2 columnX is = 1
I find it so weird. Is there a database property that affects the way cursors react or is there something that I am doing incorrectly ? Lastly, I would like to have a simple cursor template which simply goes through a configuration table or any table.
Transact SQL :: SET ROWCOUNT Applies To Dynamic Cursor
Jul 23, 2015i read in the SET ROWCOUNT documentation URL.... that 'The ROWCOUNT option does not affect dynamic cursors', it does affect my dynamic cursor created in a table function which looks like this :
CREATE FUNCTION MyTableFunction()
RETURNS @MyTable TABLE (MYFIELD INTEGER)
AS
BEGIN
DECLARE @xxx INTEGER
DECLARE My_Cursor CURSOR DYNAMIC FOR
[code]...
I would like the number of rows retruned by MyTableFunction limited to 2, but NOT the inside proc cursor's select !Set Rowcount 0 is forbidden in table function. I cannot use TOP in select * from MyTableFunction instead of setting ROWCOUNT to 2. I'm using SQL Server 2008 or 2012.
Transact SQL :: Removing Cursor And Updating Flag For Each Row?
Jul 15, 2015After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
Transact SQL :: Cursor To Create Multiple Dynamic Procedures
Jun 29, 2015The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.
Table A
Col1
Col2
A
Alpha
For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.
Result:
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie
Transact SQL :: Creating Stored Procedure With Cursor Loop
Sep 18, 2015I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?
Sep 29, 2015I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Jul 20, 2005Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies View RelatedCould Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
Sep 20, 2007I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
Join Cursor With Table Outside Of Cursor
Sep 25, 2007part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
Cursor Inside A Cursor
Oct 5, 2004I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors
declare Q cursor for
select systudentid from satrans
declare @id int
open Q
fetch next from Q into @id
while @@fetch_status = 0
begin
declare c cursor for
Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END
From SaTrans , systudent b where satrans.systudentid = b.systudentid
and satrans.systudentid = @id
declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money
set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
while @@fetch_status = 0
begin
set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount
insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id
end
close Q
deallocate Q
select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1
Client Side Cursor Vs Sever Side Cursor?
Jul 20, 2005I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
View 1 Replies View RelatedNeed Help With A Cursor
Jul 25, 2006I hope this is the appropriate forum for this question, if not then I apologize.
I've got a SQL Server 2000 stored procedure that returns data to be used in a crystal report in Visual Studio 2005. Most of the stored procedure works well, but there is a point where I need to calculate an average number of days been a group of date pairs.
I'm not familiar with cursors, but I think that I will need to use one to achieve the result I am looking for so I came up with the code below which is a snippet from my stored procedure. In this part of the code, the sp looks at the temporary table #lmreport (which holds all of the data that is returned at the end to crystal) and for every row in the table where the terrid is 'T' (the territory is domestic), it selects all of those territories from the territory table and loops through them to determine the date averages (by calling a nested stored procedure, also included below) for each territory and then updates #lmreport with that data.
When I try to run the stored procedure, I get "The column prefix '#lmreport' does not match with a table name or alias name used in the query." on the line indicated.
Does anyone have any idea what might be wrong or if this will even work the way I need it to?
Thank you in advance.
Cursor For
Sep 27, 2007Hi,
Declare wh_ctry_id CURSOR FOR
Is "cursor for" is a function or datatype or what is this?
Regards
Abdul
Use Of Cursor
Oct 21, 2007
I need some help with the concept of a Cursor, as I see it being used in a stored procedure I need to maintain.
Here is some code from the stored proc. Can someone tell me what is going on here. I haveleft out some of the sql, but have isolated the Cursor stuff.
Open MarketCursor -- How is MarketCursor loaded with data ?
FETCH NEXT
FROM MarketCursorINTO ItemID, @Item, @Reguest
WHILE @@FETCH_STATUS = 0BEGIN
DEALLOCATE MarketCursor
Is This Possible Without A Cursor?
Dec 5, 2007 I have something like
update table
set field = ...
where field = ...
and for each entry that was effected by this query I want to insert an entry into another table.
I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...
How Cursor Used In Asp.net
Mar 17, 2008hii have creted cursor but i want to use in my asp.net programming when some insert or delete command is work that time i want to excute my cursor how can i do that using asp.net with c# waiting for replaythanks
View 4 Replies View RelatedCursor And UDf
Sep 2, 2005Hello:
I am trying to define a cursor as follows:
DECLARE EmployeeList CURSOR FOR dbo.GetRecord(@EmployeeID,@CurrentDate)Can't I use a UDF in the CURSOR FOR ?Help please.thank you.
Need Cursor Help
Apr 4, 2006Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is that all the rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.
For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 .....
I could really use some help with this cursor:
Declare @CustomerName as char (60), @seqno as int, @BaseSeqno as intset @Baseseqno = 278300
declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbropen cfetch next from c into @CustomerNamewhile @@fetch_status=0begin set @seqno = @BaseSeqno + 1
update NXOFcustomers set custnmbr = @seqnoWhere custnmbr = @CustomerName fetch next from c into @CustomerNameend close cdeallocate c
Cursor
Apr 20, 2001I have a cursor defined as follows
Declare c_cursor Cursor
Scroll For Select card_id From cardcreator where card_every_cat = @cat_id
Open c_cursor
/* Scroll to the randomly selected row and populate into output parameters */
Fetch absolute @iRandomRecord From c_cursor Into @vi_cardid1
print @vi_cardid1
/*Need to check to fetch status if you have reached end of record set begin from first */
select @@fetch_status
if (@@fetch_status = 0)
Begin
Fetch next from c_cursor into @vi_cardid2
print @vi_cardid2
End
if (@@fetch_status = 0)
Begin
Fetch next from c_cursor into @vi_cardid3
End
print @vi_cardid3
/* Close and deallocate cursor */
Close c_cursor
Deallocate c_cursor
I need to have atleast three records. But if random value starts the cursor at the end, the cursor would not wrap to the beginning.
Is there a way to wrap the cursor to begining if its status is not zero.
Cursor
May 22, 2001Are the cursors declared and opened in stored procedures are always the server cursors?
View 1 Replies View RelatedCursor
Apr 13, 2000How can I do take the select of a stored procedure from another stored procedure.
Something like that:
PROC A
Declare c Cursor For Exec B
PROC B
Select * From Clients
Can I Not To Use Cursor?
Mar 18, 2003I am sure I am not the first one ask this.
I have got two tables, what I would like to do now is to update the second table using the values in the first table where
T1.id = T2.id, normally I have to use cursor to loop through table two to achieve this. But is it possible to do this without using cursor?
Thanks,
Alan
Cursor Help!
Apr 22, 1999This is what I am trying to do:
Table 1 has numerous resume's for each person. Each resume has a unique id.
ie: Table 1
res_id fname lname userid pwd address city state etc...
100 John Doe jd ok xxxx xxxx xx xxxx
104 Sally May sm sm ccccc cc c cc ccc
643 John Doe jd ok ssss null null
1003 John Doe jd ok 123 elm Nome AK ...
5000 Tom Cat tc tc null null null
I need to insert into Table 2 only the demographic information for each person appearing in Table 1. The catch is that Table 2 doesn't have the same unique id that appears in Table 1. userid and pwd are unique to Table 2 but are numerous in Table 1.
Table 2
new_ident userid pwd address city state etc..
10 jd ok 123 elm Nome AK ....
11 Sally May sm sm ccccc cc c cc ccc
12 Tom Cat tc tc null null null
Basically I need to choose the most current "max(res_id)" occurance for John Doe above to get only one row out of his three rows. Then I need to get all the other unique rows from table 1.
I hope that is clear. I was considering a cursor. Any ideas??
Troy
Cursor
Jun 17, 2002I am trying to build a cursor based on a query that uses a variable and cant seem to make it work..
Here is the query:
declare ob_cursor cursor for
select name
from @dbname.dbo.sysobjects
where xtype = 'U'
How could I pass this cursor declaration the database name?
Any help would be appreciated.
Cursor, Is It The Only Way?
May 18, 1999Is using cursor the only way to do update in this case.
I'm updating TableA.ID with TableB.New_id where TableA.ID =
TableB.ID. TableA has 2.5 million records and TableB has 500,000
records. Doing it this way bring the system down to it's knees, and
is taking forever. Any suggestion are welcome.
declare mrn_cur cursor for
select dealer_ident, kealer_id
from dealer
for read only
declare @result int
declare @temp_ident int
declare @temp_id int
declare @temp_var int
open mrn_cur
fetch mrn_cur into @temp_ident, @temp_id
while (@@fetch_status = 0)
begin
begin transaction
update label
set dealer_id = @temp_ident
where dealer_id = @temp_id
commit tran
fetch mrn_cur into @temp_ident, @temp_id
end
close mrn_cur
deallocate mrn_cur
go
Can I Do This Without A Cursor?
Jul 8, 2002Here's what I' trying to do...
I am writing an application for a training facility. I have three tables in particular that I'm concerned with.
Here they are with the relevent keys
1. Registrations - contains a customerid and a classid
2. Sessions - contains a classid (multiple sessions can exist for a class)
3. Attendence - contains a session and a customerid.
Whenever I insert a session record for a class, I want to automatically create a corresponding record in the attendence table for every student in the class . My only thought is to create an insert trigger on the session table than creates creates a cursor containing the customerid for every student registered in the class. Then I can walk through the cursor and insert an Attendence record for each student.
I really don't want to use a cursor if I can help it, but I can't think of a way to write an single INSERT statement to put into my trigger. Is there a way to do this without using a cursor?
Any thoughts would be appreciated.
David