Loop to perform updates on several DBs at the same time?

Question

Loop to perform updates on several DBs at the same time?

Navigation

#1 by (0 votes)

0

I'm looking for a way to make the same change to different databases that start and end with the same syntax, just change the base number.

I have the following code but it does not work for me, just update the first Base

DECLARE @numero_alumno INT
DECLARE @base_inicio varchar(10)
SET @base_inicio = 'Base_0'
DECLARE @base_fin varchar(10)
SET @base_fin = '_AM'
SET @numero_alumno = 1
DECLARE @Base_de_datos varchar(50)

WHILE @numero_alumno < 10
BEGIN
    SET @Base_de_datos = @base_inicio+cast(@numero_alumno AS Varchar(1))+@base_fin
    EXEC('USE '+ @Base_de_datos)
    UPDATE RPARAMS SET Param='Dato_que_cambiare' WHERE copar='CONFIGUR'
    SET @numero_alumno = @numero_alumno + 1
    PRINT @Base_de_datos
END

PRINT 'Realizado'
GO

sql while db

asked by Esteban Jaramillo 13.12.2017 в 13:46

source

1 answer

Someone can give me a hand with visual studio because the texbox remained transparent Empty values when bringing them from a jquery table

score 0 · Accepted Answer

The immediate solution to your problem is to dynamically generate UPDATE including the database name in the statement to identify the table, and then using EXEC to execute the UPDATE .

So, instead of:

EXEC('USE '+ @Base_de_datos)
UPDATE RPARAMS SET Param='Dato_que_cambiare' WHERE copar='CONFIGUR'

Replace these 2 sentences with this:

EXEC('UPDATE ' + @Base_de_datos + '..RPARAMS SET Param=''Dato_que_cambiare'' WHERE copar=''CONFIGUR''')

But repeating what I left in the comments, the fact that you have to use dynamic SQL in a cycle is a strong indication that the design of your model is not correct. It is not good design that the tables are repeated in different databases for each student.

Rather, it is normal to define a database, with a single structure of tables shared by all students, and that each student has their own records identified by a column AlumnoId or something like that.

For example, taking the example in your question, instead of having several tables RPARAMS in different databases, you would only need one, but with a column AlumnoId to be able to distinguish the records of each student and be able to manipulate them separately when necessary.

With this design, your problem would be solved by executing a single UPDATE sentence without the need for cycles or dynamic SQL:

UPDATE RPARAMS
   SET Param='Dato_que_cambiare'
 WHERE copar='CONFIGUR'

And if you need to update a data for a single student in particular, it would be just a matter of adding an additional filter to the sentence:

UPDATE RPARAMS
   SET Param='Dato_que_cambiare'
 WHERE copar='CONFIGUR'
   AND AlumnoId = 5