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

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



LVL 1
countrymeisterAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
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
 
countrymeisterAuthor Commented:
Great, thanks for your help and for the quick, accurate answer.
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.