Djabarov, Robert
Robert.Djabarov at usaa.com
Tue Oct 7 13:06:56 CDT 2003
Susan,
That's exactly what you'll get if you run this code, a new site number
for every record, incremented from record to record by one. The last
update will update the seed in your tblLastSiteNo table so that the next
time you run this mass update a new seed will be used to start increment
from.
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Geller
Sent: Tuesday, October 07, 2003 12:53 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]RE: Convert VBA to SQL pointers
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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com