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'