Updating multiple databases

Posted by / 01-Apr-2019 22:47

Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.Here is a list of the compatibly levels (versions) that you will see: To check the compatibility level of your databases you can use one of these methods: Using SQL Server Management Studio, right click on the database, select "Properties" and look at the "Options" page for each database as the following image shows: Another option is to use sp_helpdb so you can get the information for all databases at once: When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use.So, given a list of updates to apply we could effect them using the following steps: So in the example above we can reduce five statements to four. But now the number of statements is no longer directly dependent on the number of rows requiring updates.Even if we wanted to update a thousand rows with different values, we could still do it with four statements.A requirement arises in many systems to update multiple SQL database rows.For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.

A more effective solution to this problem is to attempt to reduce the number of UPDATE statements.Let us start with a simple table: UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1200 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1200 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; UPDATE staff SET salary = 1200 WHERE name = ' Bob'; UPDATE staff SET salary = 1250 WHERE name = ' Jane'; UPDATE staff SET salary = 1200 WHERE name = ' Frank'; UPDATE staff SET salary = 1250 WHERE name = ' Susan'; UPDATE staff SET salary = 1200 WHERE name = ' John'; We are no longer setting all the salary fields to the same value, so we can’t collapse it into a single statement.But we can group the updates according to the value being set, and then do one UPDATE statement per distinct value.This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

updating multiple databases-14updating multiple databases-50updating multiple databases-5

One thought on “updating multiple databases”