Home / Questions / 124443
Accepted
SeWaNsX
Asked: 2020-12-14 04:46:13 +0800 CST 2020-12-14 04:46:13 +0800 CST

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

  • 772

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 doesn't work for me, it only updates 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

1 Answers

  1. Best Answer

    The immediate solution to your problem is to generate the UPDATEdynamically by including the database name in the statement to identify the table, and then using EXECthe UPDATE.

    That is, instead of:

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

    Replace these 2 statements with this one:

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

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

    Rather, the normal thing is that you define a database, with a single table structure shared by all the students, and that each student has their own records identified by a column AlumnoIdor something similar.

    For example, taking the example in your question, instead of having several tables RPARAMSin different databases, you would only need one, but with a column AlumnoIdto 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 statement UPDATEwithout the need for loops or dynamic SQL:

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

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

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