How to add additional rows to result set by condition

Question

I have a table like this:

ObjId Date Value
100 '20100401' 12
200 '20100501' 45
200 '20100401' 37
300 '20100501' 75
300 '20100401' 69
400 '20100401' 87

I have to add additional rows to result set for objId's, where there is no data at '20100501'

**100 '20100501' null**
100 '20100401' 12
200 '20100501' 45
200 '20100401' 37
300 '20100501' 75
300 '20100401' 69
**400 '20100501' null**
400 '20100401' 87

What is the best way to do this?

Here is the T-SQL script for the initial table:

declare @datesTable table (objId int, date smalldatetime, value int)
insert @datesTable
select 100, '20100401', 12
union all
select 200, '20100501', 45
union all
select 200, '20100401', 37
union all
select 300, '20100501', 75
union all
select 300, '20100401', 69
union all
select 400, '20100401', 87

select * from @datesTable

Answer 1

OK, I understand now :-) You want to find the objId values which have no entry with a date of 2010-05-01 and then insert extra rows with those objId and that date and a NULL value - use a CTE (Common Table Expression):

;WITH MissingObj AS
(
    SELECT objId 
    FROM @datesTable  d1
    WHERE NOT EXISTS (SELECT objId FROM @datesTable d2 WHERE d2.objId = d1.objId AND d2.date = '20100501')
)
INSERT INTO @datesTable(objId, date, value)
    SELECT
        mo.objId, '20100501', NULL
    FROM    
        MissingObj mo

The MissingObj CTE gets all the objId values where there's no entry for '2010-05-01', and then using that list of objId, the following INSERT statement inserts those into your @datesTable table.

As a side-note: I find this approach much easier for filling up sample tables:

declare @datesTable table (objId int, date smalldatetime, value int)

insert into @datesTable(objId, date, value)
   VALUES(100, '20100401', 12),
     (200, '20100501', 45),
     (200, '20100401', 37),
     (300, '20100501', 75)  -- and so forth

SQL Server 2008 allows you to pass in multiple tuples of values in (....) - much easier and more readable than a union all construct...

Answer 2

This can be a solution:

declare @datesTable table (objId int, date smalldatetime, value int)
insert @datesTable
select 100, '20100401', 12
union all
select 200, '20100501', 45
union all
select 200, '20100401', 37
union all
select 300, '20100501', 75
union all
select 300, '20100401', 69
union all
select 400, '20100401', 87


with objids as (
    select distinct objid
    from @datesTable )
insert into @datesTable(objId, date, value)
select D.objId, '20100501', NULL
from objids D 
where not exists(select * 
    from @datestable T
    where T.objId = D.objId
    and date = '20100501')

select *from @datesTable