Updating database from another database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating database from another database

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-14 : 10:21:32

I have two databases

databaseA
---------

Table debtor
------------

Fields
------
Text1 varchar(100)
Text2 varchar(100)

and

databaseB
---------

Table debtor
------------

Fields
------
Text1 (varchar 200)
Text2 (varchar 100)

I want to update the table debtor in database B so that the field Text1 is a concantenation of the fields Text1 and Text2 from database A.

How can I do this please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-14 : 10:23:27

Are the databases on the same sql server?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 13:41:26

if on same server just use


USE databaseB
GO
UPDATE t
SET t.Text1=COALESCE(t1.Text1,'')+COALESCE(t1.Text2,'')
FROM [Table debtor] t
INNER JOIN databaseA.[Table debtor] t1
ON t1.LinkCol=t.LinkCol

where linkcol is column by which tables are related

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-14 : 13:50:57

My apologies, I should have said that the databases are on different servers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 13:52:41

then you need to add one as linked server to other and use like what i've shown below


USE databaseB
GO
UPDATE t
SET t.Text1=COALESCE(t1.Text1,'')+COALESCE(t1.Text2,'')
FROM [Table debtor] t
INNER JOIN server.databaseA.schema.[Table debtor] t1
ON t1.LinkCol=t.LinkCol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources