?
Solved

SQL Query - sql 2005/2008 - data migration

Posted on 2011-10-20
3
Medium Priority
?
141 Views
Last Modified: 2012-05-12
Hi,

Having the following tables:

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

tableB:
column: B_ID, BDesc

TableC
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
0
Comment
Question by:shmz
3 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 37006346
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.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37020138
UPDATE c
SET
    c.AutoIDoFA = a.A_IDPK
FROM tableC c
INNER JOIN tableA a ON
    a.A_X = c.C_A_X
0
 

Author Closing Comment

by:shmz
ID: 37067457
Tanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 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