Susan Geller
sgeller at cce.umn.edu
Tue Oct 7 12:53:12 CDT 2003
Robert,
The code you excerpt here is part of the loop. Each time the loop is
gone through, a new site number is used. Each row is updated with a
distinct and incremental siteno which is the point here.
--Susan
-----Original Message-----
From: Djabarov, Robert [mailto:Robert.Djabarov at usaa.com]
Sent: Thursday, October 02, 2003 3:52 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]RE: Convert VBA to SQL pointers
I'm probably missing something (which is not unusual), but wouldn't a
simple update do the trick?
update t set siteno = t3.site_no + t2.newsiteno
from tblLastSiteNo t3, tblAmerican t
inner join (
select t1.siteno, newsiteno=count(*)
from tblAmerican t1
inner join tblAmerican t2
on t1.id >= t2.id
group by t1.siteno
) t2
on t.siteno = t2.siteno
update tblLastSiteNo set site_no = (select max(siteno) from tblAmerican)
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Nicholson, Karen
Sent: Thursday, October 02, 2003 4:09 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]RE: Convert VBA to SQL pointers
Here you go! A simple cursor stored procedure to number records in a
table sequentially in the site_no field or whatever I called it. I put
all kinds of English in here for how the cursor is thinking. Hope this
helps. It took me a while to research this..... and it WORKED!!!!!
CREATE PROCEDURE newcursor AS
/*First, declare the cursor and the dataset unique records that you need
to loop through*/
declare mycursor CURSOR for
select id,
siteno
from tblamerican
/* Now we can open the cursor*/
open mycursor
/*Declare two like fields for comparison of unique row*/ declare
@tmp_site_id int,
@siteno int
/*Get your first row*/
fetch next from mycursor into @tmp_site_id,
@siteno /*Continue
looping through until the end of the recordset*/
while @@fetch_status = 0
begin
/* My goal is to number my site nos, starting with the next site number
in my tblLastSiteNo table */
update tblamerican
set siteno=tblLastSiteNo.site_no+1
from
/* See this next line, where we marry the two raw tables and then the
cursor rows?*/
tblLastSiteNo,
tblAmerican
where tblAmerican.id = @tmp_site_id and
tblAmerican.siteno=@siteno
/*Now I am updating that tblLastSite before I leave this row*/
update tblLastSiteNo
set site_no= site_no+1
/*Continue on my merry way through the end*/
fetch next from mycursor into @tmp_site_id,
@siteno
end
/*Clean House*/
close mycursor
deallocate mycursor
-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Thursday, October 02, 2003 3:35 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]RE: Convert VBA to SQL pointers
Thanks - I would appreciate that.
David
At 2/10/2003, you wrote:
>I posted a cursor question on Monday or Tuesday. I have since studied
>the world of cursors. They should not be used, unless in cases such as
>these where you need to move through each row like we did in the old
>days. I am almost done with my cursor hell, and think by tomorrow I
>will have it working. I have my code documented pretty well, so I will
>e it to you when I am done.
>
>-----Original Message-----
>From: David Emerson [mailto:davide at dalyn.co.nz]
>Sent: Thursday, October 02, 2003 3:19 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]RE: Convert VBA to SQL pointers
>
>
>Thanks David. I think the key word is cursor. This seems to be the
>SQL equivalent of a recordset. I will investigate this further.
>
>David
>
>At 2/10/2003, you wrote:
>
> >I would say yes, definitely. There are a few tools in sqlserver that
will
> >help you, but I didn't read through your code enough to give any
> >concrete proposals. You can avoid moving any data over the wire to
> >the front end
by
> >using stored procedures. I would modularize the code here to have
> >one calling procedure that perhaps uses a cursor or table udf to
> >identify the records you want to act on, then call sub procedures to
> >insert the
records
> >you want, passing appropriate parameters. You could have one sub
procedure
> >per insert, thereby making your code a bit easier to manage.
> >
> >You could also look at triggers to see if they can be any use here.
> >I am not super experienced, but I have read that triggers are sort of
old-school
> >db technology, and may be on their way out, so you might not want to
> >go
>down
> >that road. I have seen from my own experience that triggers are
> >trickier
>to
> >get right.
> >
> >At any rate, I don't think any of the code you posted needs to be
> >done on the front end.
> >
> >hth. D. Lewis
> >
> >
> > > Message: 2
> > > Date: Thu, 02 Oct 2003 10:39:17 +1200
> > > From: David Emerson <davide at dalyn.co.nz>
> > > Subject: [dba-SQLServer]Convert VBA to SQL pointers
> > > To: dba-SQLServer at databaseadvisors.com
> > > Message-ID: <5.2.0.9.0.20031002102433.00b1add0 at mail.dalyn.co.nz>
> > > Content-Type: text/plain; format=flowed; charset=us-ascii
> > >
> > > Group,
> > >
> > > AXP FE, SQL2000 BE.
> > >
> > > I am looking for pointers at this stage as how I can do the task.
> > > I have a vba procedure which runs on the click of a button (most
> > > of it is replicated
> > > below). As it is currently dealing with over 2000 meters it
> > > takes a while
> > > to run (because it is going back and forth between Access and
SQL).
> > >
> > > Basically what it does is loop through one recordset of meters,
> > > looks up values from other related tables, then adds records to a
> > > couple of other
> > > tables. It is things like looping through a recordset, and
> > > checking that
> > > records don't exist before creating new records that I am unsure
of.
> > >
> > > Is there a way to write the same thing in SQL so that it can all
> > > be run in the BE?
> > >
> >_______________________________________________
> >dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com