Looping through tables where the name increments monthly

Question

I have a table that is missing 18 months worth of data in one column (it is currently 0). I have found the data on another database but the tables I am getting the data from are created monthly. I could update the table by manually changing the table name and date range 18 times but I would rather use a loop (or something better) for this. I'm using SQL Server. My actual problem is a little more complicated but I was hoping to get some points to get me started.

My example is something like this -

UPDATE customers
SET Account_Number = (SELECT Account_Number
                      FROM Hist01_12 -- I need this to increase by one month every loop until Hist06_13
                      WHERE Hist01_12.Account_Number = customers.Account_Number) -- I need this to increase by one month every loop until Hist06_13
WHERE Account_Number = 0
AND Date between '2012-01-01' and '2012-01-31' ;  -- I need these months to increase until '2013-06-01' and '2013-06-30'

Answer 1

Here is a quick solution

DECLARE @command VARCHAR(8000)
DECLARE @Date DATE = '2012-01-01'
DECLARE @Year SMALLINT
DECLARE @Month SMALLINT

WHILE @Date <= '2013-06-30'
BEGIN
    SET @Year = Year(@Date)
    SET @Month = month(@Date)

    SET @command = '
  UPDATE customers
  SET Account_Number = 
        (SELECT Account_Number
         FROM Hist' + right('00' + convert(VARCHAR(2), @Month), 2) + '_' 
                    + right('00' + convert(VARCHAR(4), @Year), 2) + '
         WHERE Account_Number = customers.Account_Number
        )
  WHERE Account_Number = 0  
    AND Date >= ''' + convert(varchar(10),@Date,120) + 
    ''' and < ''' + convert(varchar(10),dateadd(month, 1, @Date),120) + 
  ''';'

    PRINT @command

    SET @date = dateadd(month, 1, @Date)
END