[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert missing records into sql 2008 table

Posted on 2011-10-14
7
Medium Priority
?
251 Views
Last Modified: 2012-05-12
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.



0
Comment
Question by:thayduck
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36969857
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
 

Author Comment

by:thayduck
ID: 36975965
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36976224
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:thayduck
ID: 36977034
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 36977690
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
 

Author Comment

by:thayduck
ID: 36980658
Thanks for all your help.
0
 

Author Closing Comment

by:thayduck
ID: 36980669
Thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question