Results 1 to 3 of 3

Thread: data cleaning

  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Unanswered: data cleaning

    Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table table3
    ( ID integer
    , address varchar(255)
    )
    
    insert into table3
    select ID 
         , address
      from table1     
     where not exists
           ( select * from table2
              where ID = table1.ID )    
    union all
    select ID 
         , address
      from table2     
     where not exists
           ( select * from table1
              where ID = table2.ID )              
    union all
    select table1.ID
         , table1.address
      from table1
    inner
      join table2
        on table1.ID = table2.ID
     where table1.address <> table2.address                   
    union all
    select table2.ID
         , table2.address
      from table1
    inner
      join table2
        on table1.ID = table2.ID
     where table1.address <> table2.address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE Table1([ID] int, address varchar(255))
    CREATE TABLE Table2([ID] int, address varchar(255))
    GO
    
    INSERT INTO Table1([ID],address)
    SELECT 1, 'Here'  UNION ALL
    SELECT 2, 'There' UNION ALL
    SELECT 3, 'And Everywhere'
    
    INSERT INTO Table2([ID],address)
    SELECT 0, 'Here'  UNION ALL
    SELECT 2, 'There' UNION ALL
    SELECT 3, 'And Everywheres'
    GO
    
    
    SELECT [ID], address
      INTO Table3 
      FROM (
    	   SELECT   [ID] 
    		  , address
    	     FROM table1
    	UNION ALL   
    	   SELECT   [ID] 
    	     	  , address
    	     FROM table2) AS XXX
    GROUP BY [ID], address
      HAVING COUNT(*) = 1
    GO
    
    SELECT * FROM Table3
    GO
    
    SET NOCOUNT OFF
    DROP TABLE Table1
    DROP TABLE Table2
    DROP TABLE Table3
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •