Run update on multiple databases

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 2008 Forums

Transact-SQL (2008)

Run update on multiple databases

Author

Topic

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-21 : 10:54:54

Hello,

I have a multiple databases on one server instance, all of them having the same table structure.
I have an UPDATE query that I want to run on all of them.
How do I do that?

thanks a lot

Sachin.Nand

2937 Posts

Posted - 2011-01-22 : 02:15:13

Set up a transational replication .

PBUH

shpinoza1980
Starting Member

17 Posts

Posted - 2011-01-22 : 12:13:40

I've used this to update all databases:

declare @command1 varchar(1000)

set @command1 = 'update t1 set t1.companyID = t3.companyID
from  .dbo.BRM_Events t1
join .dbo.feeds t2 ON t1.feedID = t2.feedID
join .dbo.companies t3 ON t2.companyName = t2.feedName'

execute sp_msForEachDb @command1

but it is executed with errors since not all databases have all the appropriate tables.Can I insert some sort of an 'if'/'forEach' clause to to check if tables exists, and if not skip to the next one?

thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-22 : 13:27:11

Use the tool developed by Mladen Prajdic, http://www.ssmstoolspack.com/

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources