locked
Copying data from one table to another RRS feed

  • Question

  • User-261468222 posted

    I'm taking over a database from another employee. It wasn't designed properly, hence I'm trying to redesign it.

    We have documents, which contain the name/number/address/etc of the person who made that document.
    This person, the applicant, is often repeated, hence it's best to have a table just for applicants.

    This caused all sorts of issues with dirty data. Spaces at the end, in the beginning, capitalisation, punctuation, everything. I spent 6 hours and cleaned up the data.
    Now I just need to restructure. 

    OLD STRUCTURE:

    Documents:
    - ID
    - ApplicantName
    - ApplicantPhone
    - ApplicantCity

    NEW STRUCTURE:

    Documents:
    - ID
    - ApplicantID
    - ApplicantName (Legacy)
    - ApplicantPhone (Legacy)
    - ApplicantCity (Legacy)

    Applicants:
    - ID
    - Name
    - Phone
    - City 

    The issue:

    I need a query that will look at DOCUMENTS and see which name was in the legacy ApplicantName column. It will then search for that name in the APPLICANTS table, and put the associated ID in the DOCUMENTS.APPLICANTID column.

    How would a query like this go? I'm having trouble figuring it out in my head.

    Once the ID is populated, the legacy columns can be deleted.

    Thursday, October 17, 2013 4:27 PM

Answers

  • User-1199946673 posted

    Create the Applicant Table

    CREATE TABLE Applicants (ID COUNTER NOT NULL, Name TEXT(255), Phone TEXT(255), City TEXT(255));
    

    Create the Primary Key

    CREATE INDEX [PrimaryKey] ON [Applicants]([ID]) WITH PRIMARY

    fill the Applicants Table:

    INSERT INTO Applicants (Name, Phone, City)
    SELECT ApplicantName, ApplicantPhone, ApplicantCity
    FROM Documents
    GROUP BY ApplicantName, ApplicantPhone, ApplicantCity
    

    add the ApplicantID column to the table Document

    ALTER TABLE Applicants ADD COLUMN ApplicantID Long

    Update the ApplicantID

    UPDATE Applicants
    INNER JOIN Document
    ON  Applicants.Name = Document.ApplicantName
    AND Applicants.City = Document.ApplicantCity
    AND Applicants.Phone = Document.ApplicantPhone
    SET Document.ApplicantID = Applicant.ID

    Delete the Legacy columns

    ALTER TABLE Documents DROP COLUMN ApplicantName
    ALTER TABLE Documents DROP COLUMN ApplicantPhone
    ALTER TABLE Documents DROP COLUMN ApplicantCity





     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 6:23 PM

All replies

  • User-1199946673 posted

    Create the Applicant Table

    CREATE TABLE Applicants (ID COUNTER NOT NULL, Name TEXT(255), Phone TEXT(255), City TEXT(255));
    

    Create the Primary Key

    CREATE INDEX [PrimaryKey] ON [Applicants]([ID]) WITH PRIMARY

    fill the Applicants Table:

    INSERT INTO Applicants (Name, Phone, City)
    SELECT ApplicantName, ApplicantPhone, ApplicantCity
    FROM Documents
    GROUP BY ApplicantName, ApplicantPhone, ApplicantCity
    

    add the ApplicantID column to the table Document

    ALTER TABLE Applicants ADD COLUMN ApplicantID Long

    Update the ApplicantID

    UPDATE Applicants
    INNER JOIN Document
    ON  Applicants.Name = Document.ApplicantName
    AND Applicants.City = Document.ApplicantCity
    AND Applicants.Phone = Document.ApplicantPhone
    SET Document.ApplicantID = Applicant.ID

    Delete the Legacy columns

    ALTER TABLE Documents DROP COLUMN ApplicantName
    ALTER TABLE Documents DROP COLUMN ApplicantPhone
    ALTER TABLE Documents DROP COLUMN ApplicantCity





     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 6:23 PM
  • User-261468222 posted

    One of the most complete answers I've seen in a long time. Thank you!
    Worked like a charm. You even had the delete column queries too! :p 

    Friday, October 18, 2013 8:47 AM