SQL Query - sql 2005/2008 - data migration

Posted on 2011-10-20
Last Modified: 2012-05-12

Having the following tables:

column: A_IDPK, ADesc, A_X,B_ID(FK)

column: B_ID, BDesc

column: C_IDPK, BDesc, C_A_X, B_ID(FK),AutoIDoFA

TableA is already populated, table B is already populated.
I can use the source table to populate tableC.
The column A_X is unique identifier for table A, it also matches with C_A_X in tableC.
The source table already has a column matching C_A_X. so there is no problem to populate
this column when migrating data to tableA.
I like to write a query to update the value of C_X with  
what I need to do is to actually find the relevant A_IDPK of A_X and also put that number into tabeC.AutoIDoFA.

how can this be done.

Thanks in advance
Question by:shmz
    LVL 50

    Expert Comment

    by:Steve Bink
    Try this:

    UPDATE tableA,TableC SET TableC.AutoIDoFA=tableA.A_IDPK WHERE TableC.C_A_X=tableA.A_X;

    That should match records between tableA and TableC, where A_X=C_A_X, and put the identity PK of table A into the AutoIDoFA field of TableC.
    LVL 68

    Accepted Solution

    UPDATE c
        c.AutoIDoFA = a.A_IDPK
    FROM tableC c
    INNER JOIN tableA a ON
        a.A_X = c.C_A_X

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now