?
Solved

sql- data migraion

Posted on 2011-10-23
6
Medium Priority
?
199 Views
Last Modified: 2012-05-12
Hi,

I am migrating data from TableA to TableB:
TableB and A have same Columns plus tableB as some extra columns which are foreign key from other tables.

total number of records in tableA is 1000 and total number of records in tableB is 999.

How can I find out which one is the record that is not migrated from tableA to table B?

I have tried left join/right join but did not work.

Thanks in advance
0
Comment
Question by:shmz
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015798
You can script a left outer join on the tables with its primary keys. like following:

SELECT A.primaryKey, B.primaryKey
FROM TableA A
LEFT OUTER JOIN TableB B 
On TableA.primaryKey=TableB.primaryKey
WHERE B.primaryKey is NULL

Open in new window


Then you will find if there is any record from TableA which does not exist in TableB.
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015806
Oooo...... I missed the bottom bit. When you said left join/right join doesn't work, what do you mean?
0
 

Author Comment

by:shmz
ID: 37015809
Sorry, the problem is like this:

TableA

Number (PK)
desc
...

TableB

B_AutoIDPK
Number
desc
....
C_FK
D_FK
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 2000 total points
ID: 37016173
Try this


select Number, desc from TableA
Except
Select Number, desc from TableB

Open in new window

0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37016463
@shmz, sachinpatil10d's solution is very clean-cut as well.
0
 

Author Closing Comment

by:shmz
ID: 37067455
Excellent thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

862 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