Databases
StackOverflow2013
Postsdbo
POAdding values to an existing table by using Cursor
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdding values to an existing table by using Cursor
    primarykey
    data
    text
    <p>By running this code its working, but when I add more values to the table it's not working anymore. I appreciate any help, thank you.</p> <p>This code works perfectly:</p> <pre><code>declare @id int declare @empid int set @id = 0 declare @schedindate datetime declare @ss nvarchar(100) declare @indice nvarchar(2) declare @FromDate datetime declare @ToDate datetime declare @TimeInR datetime declare @TimeOutR datetime declare @departmentID int declare @PositionID int declare @BranchID int declare @SupervisorID int declare @GradeID int declare @Custom1ID int declare @Custom2ID int declare @PayClassID int declare @EmploymentType int set @FromDate = '2009-01-14' set @ToDate = '2010-01-30' delete from table1 declare cc cursor for select distinct empid from ta_timecard where schedindate between @FromDate and @ToDate open cc fetch next from cc into @empid while (@@fetch_status = 0) begin set @id = @id + 1 insert into table1 (ID, EmpID) values (@id, @empid) declare cc2 cursor for select distinct departmentid from ta_timecard where empid = @empid and schedindate between @FromDate and @ToDate open cc2 fetch next from cc2 into @departmentID while (@@fetch_status = 0) begin set @indice = cast(datediff(day, @fromdate, @schedindate) as nvarchar(4)) set @ss = 'update table1 set departmetid = ' + convert(nvarchar(4), @departmentID) + ' where empid = ' + convert(nvarchar(4), @empid) execute sp_executesql @ss fetch next from cc2 into @departmentID end close cc2 deallocate cc2 fetch next from cc into @empid end close cc Deallocate cc GO </code></pre> <p><img src="https://i.stack.imgur.com/5dTo2.jpg" alt="enter image description here"></p> <p>But when I add more values to the table I got the first row only affected </p> <p><img src="https://i.stack.imgur.com/LU8k8.jpg" alt="enter image description here"></p> <pre><code>declare @id int declare @empid int set @id = 0 declare @schedindate datetime declare @ss nvarchar(100) declare @indice nvarchar(2) declare @FromDate datetime declare @ToDate datetime declare @TimeInR datetime declare @TimeOutR datetime declare @departmentID int declare @PositionID int declare @BranchID int declare @SupervisorID int declare @GradeID int declare @Custom1ID int declare @Custom2ID int declare @PayClassID int declare @EmploymentType int set @FromDate = '2009-01-14' set @ToDate = '2010-01-30' delete from table1 declare cc cursor for select distinct empid from ta_timecard where schedindate between @FromDate and @ToDate open cc fetch next from cc into @empid while (@@fetch_status = 0) begin set @id = @id + 1 insert into table1 (ID, EmpID) values (@id, @empid) declare cc2 cursor for select distinct departmentid, branchid from ta_timecard where empid = @empid --and schedindate between @FromDate and @ToDate open cc2 fetch next from cc2 into @departmentID, @BranchID--,@PositionID while (@@fetch_status = 0) begin set @indice = cast(datediff(day, @fromdate, @schedindate) as nvarchar(4)) set @ss = 'update table1 set departmetid = ' + convert(nvarchar(4), @departmentID) +', branchid = ' + convert(nvarchar(4), @BranchID) --+ ', positionid = ' + convert(nvarchar(4), @PositionID) + ' where empid = ' + convert(nvarchar(4), @empid) print(@ss) execute sp_executesql @ss fetch next from cc2 into @departmentID, @BranchID--, @PositionID end close cc2 deallocate cc2 fetch next from cc into @empid end close cc Deallocate cc GO </code></pre> <p><strong>Edited:</strong> this is the table ta_TimeCard</p> <p><img src="https://i.stack.imgur.com/o6mLL.jpg" alt="enter image description here"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNejthe
    1. USNejthe
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAdding values to an existing table by using Cursor
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAdding values to an existing table by using Cursor
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO1) Try not to use dynamic SQL when you can do without it; you can do without it in this case. 2) Cursors are evil. Cursors in cursors are evil squared. 3) Learn about CTEs; they are your friends. 3.1) http://stackoverflow.com/questions/6824669/why-is-cte-better-than-cursor-derived-table-subqueries-temp-table-etc 3.2) http://pratchev.blogspot.com/2008/03/updates-with-cte.html
      singulars
      1. POAdding values to an existing table by using Cursor
      1. USGibron
    2. COwhat does `select distinct departmentid, branchid from ta_timecard where empid = @empid` return on the second query for the second cursor?
      singulars
      1. POAdding values to an existing table by using Cursor
      1. USGibron
    3. COit should return a list of int, sorry I don't know if I didn't understand what you mean
      singulars
      1. POAdding values to an existing table by using Cursor
      1. USNejthe
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload