[dba-SQLServer] Append several fields into one

I have a solution for this john, but I won't be able to get to it for a bit
with some air code.. but essentially what you want to do is to exercise the
rowcount setting (ie,  SET ROWCOUNT 1000 and you can also add in a delay
such as WAITFOR DELAY '00:00:30'   for a 30 second delay maybe shorter if
needed ... you can also setup your select statements that you need to run
with the table hints UPDLOCK and READPAST that way they don't read rows that
are locked and still updating.

i'll have a working solution sometime after work..


-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Tue, Jun 23, 2009 at 8:27 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> This certainly looks like the answer.  Now to turn the computer loose for a
> week, updating the
> address field...
>
> There are about 6 or 8 of these address parts, each will now be inside of
> an IsNull().  ;)
>
> Is it possible to do this X records at a time?  IOW I have a fifty million
> record table.  I want to
> cause the computer to go off and start doing this but I also want the table
> that is being updated to
> be available for use while this is happening (for selection queries).  How
> do I tell SQL Server to
> do updates in blocks of 1000 records or something like that?  Can I
> dynamically change that "number
> of records updated at a time" inside of a stored procedure?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Francisco Tapia wrote:
> > John, you can do it right out of a select, something like this
> >
> > IsNull(StreetNumber + ' ', '') + IsNull(Direction + ' ', '') +
> > IsNull(StreetName + ' ', '') etc...
> >
> > I added the space padding right inside of the IsNull formula because this
> > way you get a padding, for each field, unless there is nothing then the
> > result is no padding.  In Sql Server Null + a value = Null :)
> >
> > If you don't want to accidentally end up with a space padding on the
> right
> > simply trim your statement with RTRIM(your isnull series)
> >
> >
> >
> > -Francisco
> > http://sqlthis.blogspot.com | Tsql and More...
> >
> >
> > On Tue, Jun 23, 2009 at 6:53 AM, jwcolby <jwcolby at colbyconsulting.com
> >wrote:
> >
> >> I am trying to append all of the pieces of an address into a single
> address
> >> string.  As an example,
> >> if I have the address:
> >>
> >> 121 S. Main Street NE Apt #201
> >>
> >> this address will be stored with standardized spellings of all the parts
> >> into a set of fields:
> >>
> >> 121
> >> S
> >> Main
> >> St
> >> NE
> >> Apt
> >> 201
> >>
> >> I need to get this all back into a single field.  Understand however
> that
> >> lots of those pieces might
> >> be missing in any given address.  For example the next field might be:
> >>
> >> 121
> >>
> >> Main
> >> St
> >>
> >> Ste
> >> 1342
> >>
> >> So... I need to get all the pieces back into a single address field.  If
> a
> >> piece exists then it
> >> needs padding around it so I don't end up with:
> >>
> >> 121MainStSte1342
> >>
> >> But if a piece does not exist then I do not want the padding for that
> >> missing field.
> >>
> >> I am assuming that I will be using a stored procedure.  Pass in the
> >> database, table,
> >> UnifiedAddressField, then all of the field parts.
> >>
> >> It seems clumsy to do it this way, not to mention record by record, for
> 50
> >> million records... well...
> >>
> >> There must be a better way.  Has anyone on the list done this before?
> >>
> >> --
> >> John W. Colby
> >> www.ColbyConsulting.com
> >> _______________________________________________
> >> dba-SQLServer mailing list
> >>
>