SQL 2008 - Update mulitple databases with 1 script 2

[ITALIAORIANA]

Hi Everyone,

I am going to apologize in advance if this posts twice. The first time I posted it didn't show up.

My knowledge of SQL is beginner level. My job only requires simple insert and update queries. I have now been tasked with working change requests that require updates to all 18 client databases.

We have 18 separate client databases on 5 different servers. Each server has all 18 databases. One server for test, one for config, one for dev etc...

I was wondering if it is possible to update all 18 databases on one server with one script. I tried the USE [db1config] and just added each database with a comma followed by my insert statement but that did not work. I separated each into its own USE statement but it only updated the last database. Any help would be greatly appreciated.

Thanks
Deana

 

[gmmastros]

It is not possible to update data in multiple databases with a single t-sql command. However, you can create a single script that issues 18 separate t-sql commands to update the data in each database. It would look something like this:

Update [databasename1].[dbo].TableName
Set    Column.....etc....

Update [databasename2].[dbo].TableName
Set    Column.....etc....

Update [databasename3].[dbo].TableName
Set    Column.....etc....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

You could use dynamic sql if all the structures are the same.

like it suggests here:

http://stackoverflow.com/questions/3206304/loop-through-databases-on-server-and-update-data

But be careful and make sure you have a good backup. You may also want it to exclude any other system databases....

Do not take a script that can update every database lightly.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[ITALIAORIANA]

Thanks to both of you! This will definitely cut down on time spent deploying scripts.

Deana