MS SQL Server - Updating a colum from another database

Author

Updating a colum from another database

John Steen

2006-10-24, 6:28 pm

I have two databases (DB1 and DB2) on two different servers. Both have
identical schemas but different data. There is a string of data in a varchar
column of DB1 that I need to add to the corresponding column in DB2.

For instance, the column in DB 1 has the following data:

cherries
blueberries

The column in DB2 has:

apples
oranges

I need to add cherries and blueberries to the column in DB2 .

This is something that I will have to schedule to run every day. The new
data (cherries and blueberries) comes from a difference source than the old
data (apples and oranges), and the developer who wrote the application that
extracts the old data from a form is no longer available to modify the code
to collect new data. Nor is there anyone esle avaiable, so it's fallen to
me, and I'm not a programmer.

So, can anyone help with a simple script that will do this?

Many thanks in advance!

John

Paul Ibison

2006-10-24, 6:28 pm

I'd set up a linked server and do something like this:

insert into server2.db2..tablex(fruits)
select fruits from server1.db1..tablex
except
select fruits from server2.db2..tablex

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Arnie Rowland

2006-10-24, 6:28 pm

As I understand the question, you want to keep the current values in DB2, and append the new values from DB1.

If that is correct, try something like this:

UPDATE db2
SET db2.MyColumn = ( db2.MyColumn + ', ' db1.MyColumn )
FROM DB2.dbo.MyTable db2
JOIN DB1.dbo.MyOtherTable db1
ON db1.PKColumn = db2.PKColumn
WHERE {criteria}

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"John Steen" < moderndads(nospam)@h
otmail.com> wrote in message news:B1052157-EF99-4A75-BA20- CA41C78692A0@microso
ft.com...
>I have two databases (DB1 and DB2) on two different servers. Both have
> identical schemas but different data. There is a string of data in a varchar
> column of DB1 that I need to add to the corresponding column in DB2.
>
> For instance, the column in DB 1 has the following data:
>
> cherries
> blueberries
>
> The column in DB2 has:
>
> apples
> oranges
>
> I need to add cherries and blueberries to the column in DB2 .
>
> This is something that I will have to schedule to run every day. The new
> data (cherries and blueberries) comes from a difference source than the old
> data (apples and oranges), and the developer who wrote the application that
> extracts the old data from a form is no longer available to modify the code
> to collect new data. Nor is there anyone esle avaiable, so it's fallen to
> me, and I'm not a programmer.
>
> So, can anyone help with a simple script that will do this?
>
> Many thanks in advance!
>
> John

John Steen

2006-10-24, 6:28 pm

That's exactly what I want to do, Arnie.

Thanks.

"Arnie Rowland" wrote:
[color=darkred]
> As I understand the question, you want to keep the current values in DB2, and append the new values from DB1.
>
> If that is correct, try something like this:
>
> UPDATE db2
> SET db2.MyColumn = ( db2.MyColumn + ', ' db1.MyColumn )
> FROM DB2.dbo.MyTable db2
> JOIN DB1.dbo.MyOtherTable db1
> ON db1.PKColumn = db2.PKColumn
> WHERE {criteria}
>
>
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "John Steen" < moderndads(nospam)@h
otmail.com> wrote in message news:B1052157-EF99-4A75-BA20- CA41C78692A0@microso
ft.com...

John Steen

2006-10-24, 6:28 pm

I'll give that a try, Paul. Thanks!

"Paul Ibison" wrote:

> I'd set up a linked server and do something like this:
>
> insert into server2.db2..tablex(fruits)
> select fruits from server1.db1..tablex
> except
> select fruits from server2.db2..tablex
>
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>

Sponsored Links