1

I have the following table example data

Customer     Location     ID    Attribute1     Attribute2
  Cust1        Loc1       1        High          None
  Cust1        Loc1       2        High          None
  Cust1        Loc1       3        Low           None

Based on above example data, the first 2 records are duplicate and the last one is non-duplicate. So, I need to create two tables, one for non-duplicate records and one for duplicate. The no. of Attribute columns shown here is just example, usually it's around 10 columns.

Table One

Customer     Location     ID    Attribute1     Attribute2
  Cust1        Loc1       1        High          None

Table Two

Customer     Location     ID    Attribute1     Attribute2
  Cust1        Loc1       3        Low           None

Can this be performed in one SQL query?

Thanks for any suggestions, Javid

user320587
  • 1,347
  • 7
  • 29
  • 57
  • 4
    You can't insert into (or create) 2 different tables in one query, no. Why are you splitting it into 2 tables anyway? What happened to Id number 2? Is this just an intermediate step to remove duplicates? [If so see this answer](http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows/3822833#3822833) – Martin Smith Mar 31 '11 at 21:46
  • Thanks Martin. You pointed me to the link I was exactly looking for. – user320587 Apr 01 '11 at 14:32

1 Answers1

0

you can do this very easily....

(I have run this below query in oracle database I am not sure abour sql server but in sql sentence point of view it is doing right operation )

insert all
when (Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2) in 
     (select Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2 
         from base_table 
         group by Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2 
         having count(*)>1) then 
   into Table_One (Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2)
      values (Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2)
else
   into table_two (Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2 )
       value (Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2 ) 
select distinct Customer ,    Location ,    ID ,   Attribute1 ,    Attribute2 from base_table

this query is useful when you have all column values duplicate...

but in your question what you have given as example data you don't have duplicate ROWS..

see ID column values..

please tell here if you want to check for specific column group for the same..

pratik garg
  • 3,282
  • 1
  • 17
  • 21
  • Though the ID column has different values, I need to ignore and treat the first 2 records as duplicates. I managed to create a solution using the link pointed in the comments to my original post. – user320587 Jun 29 '11 at 04:13
  • I saw that link .. but thought that you might be still looking for insert into different tables rather then delete duplicate records... i have used this kind of working in my project to store duplicate values as well .... and one more thing about non duplicate rows issue... if you want to take group of columns only for checking then you can just take that column groups only in group by and distinct clause... :) – pratik garg Jun 29 '11 at 05:11