Replacing data from another database

Hello all,
I have a seems to be simple task but haven't figured out the best way to
approach it.
I have 2 address tables: Address and CleanedAddresses
Address has all the address fields with additional fields such as type,
description, etc. CleanedAddresses has only address info, they both have
the same ID. On Address its an identity on CleanedAddresses it's an INT.
I want to cycle through Address and update the addresses on it with the
addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
page to do so and it never finished, I think due to connectivity issues.
There are about 12,000 addresses. Do you have any suggestions on how I
should do this task? Thanks ahead of time.
ShawnShawn,
How about this?
UPDATE Address
SET AddressLine1 = CleanedAddresses.AddressLine1
-- More Columns as needed
FROM CleanedAddresses
WHERE Address.ID = CleanedAddresses.ID
RLF
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||Give unto Caesar that which is Caesar's.
Write it in T-SQL and run it through SQLagent.
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||What do you mean? Take it through SQLAgent to do what? How about looping
through every record?
"Jay" <spam@.nospam.org> wrote in message
news:OQ%23wtN5%23HHA.4956@.TK2MSFTNGP06.phx.gbl...
> Give unto Caesar that which is Caesar's.
> Write it in T-SQL and run it through SQLagent.
>
> <programmingcodeATjards.com> wrote in message
> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way to
>> approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both have
>> the same ID. On Address its an identity on CleanedAddresses it's an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions on
>> how I should do this task? Thanks ahead of time.
>> Shawn
>|||Thanks,
How do you loop through all the address records? From the beginning of the
file to the end. I didn't know you could access the other table by using
tablename.fieldname, thanks for the insight.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%236Nf$K5%23HHA.5464@.TK2MSFTNGP02.phx.gbl...
> Shawn,
> How about this?
> UPDATE Address
> SET AddressLine1 = CleanedAddresses.AddressLine1
> -- More Columns as needed
> FROM CleanedAddresses
> WHERE Address.ID = CleanedAddresses.ID
> RLF
> <programmingcodeATjards.com> wrote in message
> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way to
>> approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both have
>> the same ID. On Address its an identity on CleanedAddresses it's an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions on
>> how I should do this task? Thanks ahead of time.
>> Shawn
>|||Shawn,
This does not loop through at all. Instead of row-at-a-time processing it
processes the entire set of qualifying rows.
It joins the Address and CleanedAddresses tables for the row where the ID
matches, which you defined as the case. Therefore, this one statement
updates every Address row that has a matching CleanedAddresses row.
RLF
<programmingcodeATjards.com> wrote in message
news:%238RaHf5%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> How do you loop through all the address records? From the beginning of
> the file to the end. I didn't know you could access the other table by
> using tablename.fieldname, thanks for the insight.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%236Nf$K5%23HHA.5464@.TK2MSFTNGP02.phx.gbl...
>> Shawn,
>> How about this?
>> UPDATE Address
>> SET AddressLine1 = CleanedAddresses.AddressLine1
>> -- More Columns as needed
>> FROM CleanedAddresses
>> WHERE Address.ID = CleanedAddresses.ID
>> RLF
>> <programmingcodeATjards.com> wrote in message
>> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way
>> to approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both
>> have the same ID. On Address its an identity on CleanedAddresses it's
>> an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions
>> on how I should do this task? Thanks ahead of time.
>> Shawn
>>
>|||easiest way to do this would just be with a simple T-sql statement...
update address
set address.field1 = cleanedaddress.field1,
address.field2 = cleanedaddress.field2, etc, etc
from address
inner join cleanedaddress
on address.id = cleanedaddress.id
is there some reason that you couldn't do it like that?
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
<programmingcodeATjards.com> wrote in message
news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I have a seems to be simple task but haven't figured out the best way to
> approach it.
> I have 2 address tables: Address and CleanedAddresses
> Address has all the address fields with additional fields such as type,
> description, etc. CleanedAddresses has only address info, they both have
> the same ID. On Address its an identity on CleanedAddresses it's an INT.
> I want to cycle through Address and update the addresses on it with the
> addresses on CleanedAddresses - simple enough huh. I tried writing an ASP
> page to do so and it never finished, I think due to connectivity issues.
> There are about 12,000 addresses. Do you have any suggestions on how I
> should do this task? Thanks ahead of time.
> Shawn
>|||What I mean is: you are attempting to do database maintenance in an ASP
page. This is a bad idea. The ASP page will have a heavier footprint on your
overall infrastructure and your code will be limited.
Since what you need is SQL, just write a procedure to do what you want, thus
keeping everything inside the database, where it will work betrter anyway.
Then, if you want to schedule the task to run repeatably, schedule the job
in SQLagent.
<programmingcodeATjards.com> wrote in message
news:usygie5%23HHA.700@.TK2MSFTNGP05.phx.gbl...
> What do you mean? Take it through SQLAgent to do what? How about
> looping through every record?
>
> "Jay" <spam@.nospam.org> wrote in message
> news:OQ%23wtN5%23HHA.4956@.TK2MSFTNGP06.phx.gbl...
>> Give unto Caesar that which is Caesar's.
>> Write it in T-SQL and run it through SQLagent.
>>
>> <programmingcodeATjards.com> wrote in message
>> news:%23xACU94%23HHA.4184@.TK2MSFTNGP05.phx.gbl...
>> Hello all,
>> I have a seems to be simple task but haven't figured out the best way
>> to approach it.
>> I have 2 address tables: Address and CleanedAddresses
>> Address has all the address fields with additional fields such as type,
>> description, etc. CleanedAddresses has only address info, they both
>> have the same ID. On Address its an identity on CleanedAddresses it's
>> an INT.
>> I want to cycle through Address and update the addresses on it with the
>> addresses on CleanedAddresses - simple enough huh. I tried writing an
>> ASP page to do so and it never finished, I think due to connectivity
>> issues. There are about 12,000 addresses. Do you have any suggestions
>> on how I should do this task? Thanks ahead of time.
>> Shawn
>>
>