Need SQL command to compare two temp table data by composite key

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi
I have two insert script files which has around 1,00,000 records in each script file. Both files will insert the records into one table only . i mean schema of table def is one and the same.

But these two files are different in version, that is Data/content in those might be different (different in records).

Now my job is to compare these two files data and find it out wheather they have different in colums data based on primary key column.

For this, i have create two temp tables with different tables names and try to run the insert script files on appropriate tables.

So could you please suggest me or guide me how to compare the data in both the files by inserting into temp table and compareing these temp table with common primary key value.

Please give the code (sql statement).

Thanks in advance..

Thanks & Regards
Chiranjeevi Ommi.

Posted 29-Nov-12 10:22am

ommi.chiru

Add a Solution

Comments

joshrduncan2012 29-Nov-12 17:05pm

We won't "give" you anything. Have you tried this yet? If so, where did you get stuck?

chaau 29-Nov-12 19:42pm

As a small guide, JEFT JOIN and RIGHT JOIN should be your starting points

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Accepted Answer · 2012-11-29T19:48:00

Here is a sample approach.
I have created two tables with identical columns and inserted some data in both the tables. Then i have queried the tables to retrieve rows which are not same.

SQL

CREATE TABLE #Table1
(
	ID INT PRIMARY KEY,
	[Name] VARCHAR(50),
	Email VARCHAR(50)
)

CREATE TABLE #Table2
(
	ID INT PRIMARY KEY,
	[Name] VARCHAR(50),
	Email VARCHAR(50)
)


INSERT INTO #Table1
SELECT 1, 'XYZ', 'XYZ@XYZ.com' UNION ALL
SELECT 2, 'XYZ 1', 'XYZ1@XYZ.com' UNION ALL
SELECT 3, 'XYZ 2', 'XYZ2@XYZ.com' UNION ALL
SELECT 4, 'XYZ 3', 'XYZ3@XYZ.com' UNION ALL
SELECT 5, 'XYZ 4', 'XYZ4@XYZ.com' 

INSERT INTO #Table2
SELECT 1, 'XYZ', 'XYZ@XYZ.com' UNION ALL
SELECT 2, 'XYZ 1', 'XYZ13@XYZ.com' UNION ALL
SELECT 3, 'XYZ 2', 'XYZ2@XYZ.com' UNION ALL
SELECT 4, 'XYZ 3', 'XYZ3@XYZ.com' UNION ALL
SELECT 5, 'XYZ 45', 'XYZ4@XYZ.com' 

--Query to list out rows where ID is same in Table1 and Table2, but the Name or Email is different
SELECT * FROM #Table1 T1 
LEFT JOIN #Table2 T2 ON T1.ID = T2.ID
WHERE T1.[Name] <> T2.[Name] OR T1.Email <> T2.Email


DROP TABLE #Table1
DROP TABLE #Table2

Hope this helps.