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.



thayduckProgrammer AnalystAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this depends on what you will need from the other fields ...
you may read this article to get "distinct" values:
http://www.experts-exchange.com/A_3203.html

but in combination with the "date" values you surely need, it won't be "easy" necessarily...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
thayduckProgrammer AnalystAuthor Commented:
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







0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
thayduckProgrammer AnalystAuthor Commented:
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 ?

0
 
thayduckProgrammer AnalystAuthor Commented:
Thanks for all your help.
0
 
thayduckProgrammer AnalystAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.