asked on

Insert missing records into sql 2008 table

table 1:
Custno
Processdate

Table 2:
Processdate

There are thousands of records in Table 1.
There could be up to 15 different processdate records in Table 2.

I need to go thru table 1 by customer and create a new customer record if I do not find that customer record in table 1 with a processdate that is in Table 2.

So if Table 1 has :

123456 08/01/2011
234567 10/03/2011

and Table 2 has:

12/05/2010
08/31/2011
09/01/2011
10/10/2011
10/14/2011

I want table 1 to have:

123456 12/05/2010
123456 08/01/2011
123456 08/31/2011
123456 09/01/2011
123456 10/10/2011
123456 10/14/2011
234567 12/05/2010
234567 10/03/2011
234567 08/31/2011
234567 09/01/2011
234567 10/10/2011
234567 10/14/2011

after all is said and done.

Guy Hengel [angelIII / a3]

this should do:

insert into table1 ( Custno, Processdate)
select c.CustNo, p.ProcessDate
  from table1 c
  cross join table2 p
  where not exists ( select null from table1 e where e.custno = c.custno and e.ProcessDate = p.ProcessDate )

Open in new window

all said

thayduck

ASKER

I explained problem wrong to you, but the way I explained it wrong, your solutioned worked so I will award you the points

But----- Can you help me out here ? Of course I will add another 125 points for this solution.

I need to go thru table 1 by customer and create a new customer record if I do not find that customer record in table 1 with a processdate that is in Table 2.

So if Table 1 has :

123456 08/01/2011
123456 10/10/2011
123456 10/14/2011
234567 08/31/2011
234567 09/01/2011

and Table 2 has:

12/05/2010
08/31/2011
09/01/2011
10/10/2011
10/14/2011

I want table 1 to have: (basically I want to fill in the gaps)

123456 12/05/2010
123456 08/01/2011
123456 08/31/2011
123456 09/01/2011
123456 10/10/2011
123456 10/14/2011
234567 12/05/2010
234567 10/03/2011
234567 08/31/2011
234567 09/01/2011
234567 10/10/2011
234567 10/14/2011

If I use solution you gave me for this, it gives me to many records for each cust#.
I see why it is doing it but cant figure a way around.

It gives me:
123456 12/05/2010
123456 08/01/2011
123456 08/31/2011
123456 09/01/2011
123456 10/10/2011
123456 10/14/2011
123456 12/05/2010
123456 08/01/2011
123456 08/31/2011
123456 09/01/2011
123456 10/10/2011
123456 10/14/2011
123456 12/05/2010
123456 08/01/2011
123456 08/31/2011
123456 09/01/2011
123456 10/10/2011
123456 10/14/2011
234567 12/05/2010
234567 10/03/2011
234567 08/31/2011
234567 09/01/2011
234567 10/10/2011
234567 10/14/2011
234567 12/05/2010
234567 10/03/2011
234567 08/31/2011
234567 09/01/2011
234567 10/10/2011
234567 10/14/2011

Guy Hengel [angelIII / a3]

sorry, here is the fix

insert into table1 ( Custno, Processdate)
select c.CustNo, p.ProcessDate
  from (select CustNo from table1 group by CustNo ) c
  cross join table2 p
  where not exists ( select null from table1 e where e.custno = c.custno and e.ProcessDate = p.ProcessDate )

Open in new window

thayduck

ASKER

I must apologize again. I was over simplifying.

Table 1 has around 15 fields in it not just Custno and Processdate.

Will from (select CustNo from table1 group by CustNo ) c still work ?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thayduck

ASKER

Thanks for all your help.

thayduck

ASKER

Thanks