Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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



0
countrymeister
Asked:
countrymeister
1 Solution
 
chapmandewCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now