1

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'
3

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
  • Spot on Scott, it worked perfectly. Thanks for the help. – StuBow75 Feb 10 '17 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.