Solved

How to load data into a table based on select criteria from another table

Posted on 2008-10-03
2
188 Views
Last Modified: 2010-04-21
I have a table Order . I need to load data into another table OrderDuplicates based on a select criteria where there are more than one occurrence of the following two column grouping, OrderID, OrderNumber.
Order and OrderDuplicates have same structure

Order
---------
OrderID
OrderNumber
OrderDate
OrderPrice
OrderUserid

So if I have the foll data
OrderID     OrderNumber    OrderDate            OrderPrice      OrderUserid
1                21                      09/01/2008             10.00           abc
1                21                      09/02/2008             12.00            lmn
1                21                       09/03/2008             11.00           xyz
1               20                       09/01/2008                10.00m       abc
2               19                       09/01/2008               9.00            xyz
2               19                       09/02/2008               10.00            abc
3               13                      09/02/2008               10.00             xyz
5               12                      09/03/2008               11.00            abc
6               12                       09/01/2008              10.00             xyz
6               12                       09/03/2008               9.00                abx

I would want the foll rows loaded into OrderDuplicates
1                21                      09/01/2008             10.00           abc
1                21                      09/02/2008             12.00            lmn
1                21                       09/03/2008             11.00           xyz
2               19                       09/01/2008               9.00            xyz
2               19                       09/02/2008               10.00            abc
6               12                       09/01/2008              10.00             xyz
6               12                       09/03/2008               9.00                abx



0
Comment
Question by:countrymeister
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22633692
insert into orderduplicates(fieldlist)
select o.*
from
order o
join
(
select orderid, ordernumber
from order
group by orderid, ordernumber
having count(*) > 1
) d on o.orderid = d.orderid and o.ordernumber = d.ordernumber
0
 
LVL 1

Author Closing Comment

by:countrymeister
ID: 31502745
Great, thanks for your help and for the quick, accurate answer.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article I will describe the Copy Database Wizard 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.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now