需要SQL命令通过组合键比较两个临时表数据 [英] Need SQL command to compare two temp table data by composite key

问题描述

嗨
我有两个插入脚本文件，每个脚本文件中都有大约1,00,000条记录.这两个文件只会将记录插入到一​​个表中.我的意思是表def的架构是相同的.

但是这两个文件的版本不同，也就是说，这两个文件中的数据/内容可能不同(记录中也不同).

现在我的工作是比较这两个文件数据，并基于主键列找出它们在列数据中有何不同.

为此，我创建了两个具有不同表名的临时表，并尝试在适当的表上运行插入脚本文件.

因此，您可以建议我还是指导我如何通过插入到临时表中并将这些临时表与通用主键值进行比较来比较两个文件中的数据.

请提供代码(sql语句).

提前谢谢..

谢谢与问候
Chiranjeevi Ommi.

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.

推荐答案

这里是示例方法.
我创建了两个具有相同列的表，并在两个表中插入了一些数据.然后我查询了表以检索不相同的行.

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.

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', '[e-mail]' UNION ALL
SELECT 2, 'XYZ 1', '[e-mail]' UNION ALL
SELECT 3, 'XYZ 2', '[e-mail]' UNION ALL
SELECT 4, 'XYZ 3', '[e-mail]' UNION ALL
SELECT 5, 'XYZ 4', '[e-mail]' 

INSERT INTO #Table2
SELECT 1, 'XYZ', '[e-mail]' UNION ALL
SELECT 2, 'XYZ 1', '[e-mail]' UNION ALL
SELECT 3, 'XYZ 2', '[e-mail]' UNION ALL
SELECT 4, 'XYZ 3', '[e-mail]' UNION ALL
SELECT 5, 'XYZ 45', '[e-mail]' 

--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.

这篇关于需要SQL命令通过组合键比较两个临时表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！