T-SQL INSERT into UNLESS

Question

I have the following table:

Account              | Period   | Amount 
-------------------- | -------- | ------
Umbrella Corporation | 201601   | 100
Umbrella Corporation | 201602   | 50
Umbrella Corporation | 201608   | 100
Acme Inc             | 201504   | 85
Acme Inc             | 201504   | 90
Acme Inc             | 201512   | 40

[Period] is plain text, but represents a date as YYYMM and sometimes may include YYYMMDD too, but for this purpose, I ignore the day.

Goal

Insert new row for each unique combination of [Account] and the 12 possible time-periods for each year (YYYYMM).

Example

-> if the unique combination already exists in the table, then do nothing -> if the unique combination does not already exist, insert a new row with the account, period and amount (the amount always being zero for newly inserted rows).  

Desired outcome

Account                 | Period    | Amount 
--------------------    | --------  | ------
Umbrella Corporation    | 201601    | 100
Umbrella Corporation    | 201602    | 100
Umbrella Corporation    | 201603    | 0
Umbrella Corporation    | 201604    | 0
Umbrella Corporation    | 201605    | 0
Umbrella Corporation    | 201606    | 0
Umbrella Corporation    | 201607    | 0
Umbrella Corporation    | 201608    | 100
Umbrella Corporation    | 201609    | 0
Umbrella Corporation    | 201610    | 0
Umbrella Corporation    | 201611    | 0
Umbrella Corporation    | 201612    | 0
Acme Inc                | 201501    | 0
Acme Inc                | 201502    | 0
Acme Inc                | 201503    | 0
Acme Inc                | 201504    | 85
Acme Inc                | 201504    | 90
Acme Inc                | 201605    | 0
Acme Inc                | 201506    | 0
Acme Inc                | 201507    | 0
Acme Inc                | 201508    | 0
Acme Inc                | 201509    | 0
Acme Inc                | 201510    | 0
Acme Inc                | 201511    | 0
Acme Inc                | 201512    | 40

I've not been able to figure out a solid starting point to doing this. I've found somewhat similar asks here which use INSERT INTO....WHERE NOT EXISTS or MERGE or JOIN. But Ideally, I'd like to achieve this result without needing another table, if at all possible.

Any guidance would be greatly appreciated. I'm using SQL Server 2008R2.

*I've tried to make a good question, if you think it could be better, please let me know.

Answer 1

One option is to use a CTE to create a tally table to help determine the possible month intervals. The following example demonstrates this approach.

-- Create example table and sample data set.
CREATE TABLE Accounts
(
     Account    NVARCHAR(100)
    ,Period     NVARCHAR(10)
    ,Amount     FLOAT
)

INSERT INTO Accounts
(
 Account
,Period 
,Amount 
)
SELECT 'Umbrella Corporation' , '201601'   , 100    UNION ALL
SELECT 'Umbrella Corporation' , '201602'   , 50     UNION ALL
SELECT 'Umbrella Corporation' , '201608'   , 100    UNION ALL
SELECT 'Acme Inc'             , '201504'   , 85     UNION ALL
SELECT 'Acme Inc'             , '201504'   , 90     UNION ALL
SELECT 'Acme Inc'             , '201512'   , 40;

DECLARE @endPeriod NVARCHAR(10) = '201612';

WITH
E1(N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
E4(N) AS (SELECT 1 FROM E2 a, E2 b), 
Tally(N) AS     -- Create tally table. This tally table will return a maximum of a 1000 rows.
(
    SELECT  (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) FROM E4
)
, PossiblePeriods AS    --Determine possible monthly intervals for the year. Implementation assumes a maximum back fill of 5 years (60 months).
(
    SELECT   Account
            ,LEFT(CONVERT(NVARCHAR(10), DATEADD(MONTH, [T].N, [A].StartYear), 112), 6) AS Period
    FROM    (
                SELECT   Account
                        ,CONVERT(DATETIME, LEFT(MIN(Period), 4), 112) AS StartYear
                FROM    Accounts
                GROUP BY Account
            ) [A]
    CROSS JOIN 
            (
                SELECT TOP 60 N FROM Tally  -- Modify the TOP statement to control the number of months to back fill (up to a 1000 months)
            ) T
)
, NewPeriods AS -- Determine the new periods to add.
(
    SELECT   [P].Account
            ,[P].Period
            ,[A].Amount
    FROM    PossiblePeriods [P]
    LEFT OUTER JOIN Accounts [A]  ON [A].Account = P.Account AND A.Period = [P].Period
    WHERE   [P].Period <= @endPeriod
)
INSERT INTO Accounts
(
     Account
    ,Period 
    ,Amount 
)
SELECT  Account
        ,Period 
        ,0  
FROM    NewPeriods
WHERE   Amount IS NULL


--  Select out result.
SELECT      *
FROM        Accounts
ORDER BY    Account, Period

DROP TABLE Accounts

Answer 2

You can use a CROSS JOIN between the accounts and the periods:

DECLARE @StartPeriod VARCHAR(8), @EndPeriod VARCHAR(8);
SET @StartPeriod = '201501';
SET @EndPeriod = '201612';

WITH Periods AS
(
    SELECT CONVERT(VARCHAR(6),DATEADD(MONTH,number,@StartPeriod + '01'),112) Period
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND CONVERT(VARCHAR(6),DATEADD(MONTH,number,@StartPeriod + '01'),112) <= @EndPeriod
)
SELECT  A.Account,
        B.Period,
        ISNULL(C.Amount,0) Amount
FROM (  SELECT DISTINCT Account
        FROM dbo.YourTable) A
CROSS JOIN Periods B
LEFT JOIN dbo.YourTable C
    ON A.Account = C.Account
    AND B.Period = C.Period;

This solution assumes that you don't already have a table that has every period you want. If you do have one, then use that instead of creating them in the CTE.

Answer 3

Instead of using another table, you can use a recursive CTE that generates all the static values you want and JOIN your existing data to that.