두 테이블의 SQL 비교 데이터
I 2 개 테이블을 TableA하고 TableB두 테이블을 예를 들면 열 동일한 형식을 갖는 TableA과 TableB열이
A B C D E F
여기서 A와 B는 기본 키입니다.
경우 있는지 확인하기 위해 SQL을 작성하는 방법 TableA및 TableB그 동일한 기본 키가 모든 컬럼에서 정확히 같은 값을 포함합니다.
이는이 두 테이블이 정확히 동일한 데이터를 가지고 있음을 의미합니다.
DBMS에서 사용하는 SQL의 종류에 따라 "MINUS"또는 "EXCEPT"가 가능해야합니다.
select * from tableA
minus
select * from tableB
쿼리가 행을 반환하지 않으면 데이터는 정확히 동일합니다.
관계 연산자 사용 :
SELECT * FROM TableA
UNION
SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;
변경 EXCEPT에 MINUS대한 Oracle.
약간 까다로운 점 : 위의 내용은 연산자 우선 순위에 의존하며 SQL 표준에 따라 구현에 따라 다르므로 YMMV입니다. 우선 순위가 다음과 같은 SQL Server에서 작동합니다.
- 괄호 안의 식
INTERSECTEXCEPT그리고UNION왼쪽에서 오른쪽으로 평가했다.
dietbuddha는 좋은 대답을 가지고 있습니다. MINUS 또는 EXCEPT가없는 경우 한 가지 옵션은 테이블간에 모두 유니온을 수행하고 모든 열로 그룹화하고 모든 항목이 두 개 있는지 확인하는 것입니다.
SELECT col1, col2, col3
FROM
(SELECT * FROM tableA
UNION ALL
SELECT * FROM tableB) data
GROUP BY col1, col2, col3
HAVING count(*)!=2
SELECT c.ID
FROM clients c
WHERE EXISTS(SELECT c2.ID
FROM clients2 c2
WHERE c2.ID = c.ID);
두 테이블에서 동일한 모든 ID를 반환합니다. 차이점을 얻으려면 EXISTS를 NOT EXISTS로 변경하십시오.
언젠가 스크립트를 가져 와서 각 항목의 출처를 표시하도록 수정했습니다.
DECLARE @table1 NVARCHAR(80)= 'table 1 name'
DECLARE @table2 NVARCHAR(80)= 'table 2 name'
DECLARE @sql NVARCHAR (1000)
SET @sql =
'
SELECT ''' + @table1 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + '
) x
UNION
SELECT ''' + @table2 + ''' AS table_name,* FROM
(
SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 + '
) y
'
EXEC sp_executesql @stmt = @sql
완료하기 위해 except 메소드를 사용하여 저장된 proc은 2 개의 테이블을 비교하고 3 개의 오류 상태를 가진 동일한 테이블에 결과를 제공합니다. ADD, DEL, GAP 테이블은 동일한 PK를 가져야합니다. 1 개 또는 2 개의 테이블을 비교할 2 개의 테이블과 필드를 선언합니다.
다음과 같이 사용하십시오. ps_TableGap 'tbl1', 'Tbl2', 'fld1, fld2, fld3', 'fld4'fld5'fld6'(선택 사항)
/****** Object: StoredProcedure [dbo].[ps_TableGap] Script Date: 10/03/2013 16:03:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arnaud ALLAVENA
-- Create date: 03.10.2013
-- Description: Compare tables
-- =============================================
create PROCEDURE [dbo].[ps_TableGap]
-- Add the parameters for the stored procedure here
@Tbl1 as varchar(100),@Tbl2 as varchar(100),@Fld1 as varchar(1000), @Fld2 as varchar(1000)= ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Variables
--@Tbl1 = table 1
--@Tbl2 = table 2
--@Fld1 = Fields to compare from table 1
--@Fld2 Fields to compare from table 2
Declare @SQL varchar(8000)= '' --SQL statements
Declare @nLoop int = 1 --loop counter
Declare @Pk varchar(1000)= '' --primary key(s)
Declare @Pk1 varchar(1000)= '' --first field of primary key
declare @strTmp varchar(50) = '' --returns value in Pk determination
declare @FldTmp varchar (1000) = '' --temporarily fields for alias calculation
--If @Fld2 empty we take @Fld1
--fields rules: fields to be compare must be in same order and type - always returns Gap
If @Fld2 = '' Set @Fld2 = @Fld1
--Change @Fld2 with Alias prefix xxx become _xxx
while charindex(',',@Fld2)>0
begin
Set @FldTmp = @FldTmp + (select substring(@Fld2,1,charindex(',',@Fld2)-1) + ' as _' + substring(@Fld2,1,charindex(',',@Fld2)-1) + ',')
Set @Fld2 = (select ltrim(right(@Fld2,len(@Fld2)-charindex(',',@Fld2))))
end
Set @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2
Set @Fld2 = @FldTmp
--Determinate primary key jointure
--rule: same pk in both tables
Set @nLoop = 1
Set @SQL = 'Declare crsr cursor for select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '''
+ @Tbl1 + ''' or TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 + ''' or TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1
+ ''' order by ORDINAL_POSITION'
exec(@SQL)
open crsr
fetch next from crsr into @strTmp
while @@fetch_status = 0
begin
if @nLoop = 1
begin
Set @Pk = 's.' + @strTmp + ' = b._' + @strTmp
Set @Pk1 = @strTmp
set @nLoop = @nLoop + 1
end
Else
Set @Pk = @Pk + ' and s.' + @strTmp + ' = b._' + @strTmp
fetch next from crsr into @strTmp
end
close crsr
deallocate crsr
--SQL statement build
set @SQL = 'select case when s.' + @Pk1 + ' is null then ''Del'' when b._' + @Pk1 + ' is null then ''Add'' else ''Gap'' end as TypErr, '''
set @SQL = @SQL + @Tbl1 +''' as Tbl1, s.*, ''' + @Tbl2 +''' as Tbl2 ,b.* from (Select ' + @Fld1 + ' from ' + @Tbl1
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld2 + ' from ' + @Tbl2 + ')s full join (Select ' + @Fld2 + ' from ' + @Tbl2
set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld1 + ' from ' + @Tbl1 +')b on '+ @Pk
--Run SQL statement
Exec(@SQL)
END
dietbuddha의 답변 향상 ...
select * from
(
select * from tableA
minus
select * from tableB
)
union all
select * from
(
select * from tableB
minus
select * from tableA
)
Oracle에서 모두 삽입 및 전체 외부 조인 조합을 사용하여 두 테이블의 차이점을 찾을 수 있습니다. SQL에서는 완전 외부 조인을 통해 차이점을 추출 할 수 있지만 모두 삽입 / 첫 번째 삽입이 SQL에 존재하지 않는 것 같습니다! 따라서 대신 다음 쿼리를 사용해야합니다.
select * from A
full outer join B on
A.pk=B.pk
where A.field1!=B.field1
or A.field2!=B.field2 or A.field3!=B.field3 or A.field4!=B.field4
--and A.Date==Date1
where 절에서 'OR'을 사용하는 것은 권장되지 않으며 일반적으로 성능이 저하되지만 테이블이 방대하지 않은 경우에도 위의 쿼리를 사용할 수 있습니다. 위의 질의에 대한 결과가 있다면 1,2,3,4 필드를 비교 한 결과 두 테이블의 차이입니다. 쿼리 성능 향상을 위해 날짜별로 필터링 할 수도 있습니다 (코멘트 부분 확인).
SELECT unnest(ARRAY[1,2,2,3,3])
EXCEPT
SELECT unnest(ARRAY[1,1,2,3,3])
UNION
SELECT unnest(ARRAY[1,1,2,3,3])
EXCEPT
SELECT unnest(ARRAY[1,2,2,3,3])
결과가 null이지만 소스가 다릅니다!
그러나:
(
SELECT unnest(ARRAY[1,2,2,3])
EXCEPT ALL
SELECT unnest(ARRAY[2,1,2,3])
)
UNION
(
SELECT unnest(ARRAY[2,1,2,3])
EXCEPT ALL
SELECT unnest(ARRAY[1,2,2,3])
)
공장.
나는 SQL Server에서 이와 동일한 문제를 겪었고 프로세스를 자동화하기 위해이 T-SQL 스크립트를 작성했습니다 (실제로 이것은 물이 부족한 버전입니다.
비교하려는 테이블 이름으로 'MyTable'및 'MyOtherTable'을 업데이트합니다.
DECLARE @ColName varchar(100)
DECLARE @Table1 varchar(100) = 'MyTable'
DECLARE @Table2 varchar(100) = 'MyOtherTable'
IF (OBJECT_ID('tempdb..#col') IS NOT NULL) DROP TABLE #col
SELECT IDENTITY(INT, 1, 1) RowNum , c.name
INTO #col
FROM SYS.Objects o
JOIN SYS.columns c on o.object_id = c.object_id
WHERE o.name = @Table1 AND NOT c.Name IN ('List','Columns','YouWantToIgnore')
DECLARE @Counter INT = (SELECT MAX(RowNum) FROM #col)
WHILE @Counter > 0
BEGIN
SET @ColName = (SELECT name FROM #Col WHERE RowNum= @Counter)
EXEC ('SELECT t1.Identifier
,t1.'+@ColName+' AS '+@Table1+@ColName+'
,t2.'+@ColName+' AS '+@Table2+@ColName+'
FROM '+@Table1+' t1
LEFT JOIN '+@Table2+' t2 ON t1.Identifier = t2.Identifier
WHERE t1.'+@ColName+' <> t2.'+@ColName)
SET @Counter = @Counter - 1
END
Oracle에서 SQL Server로 포팅 한 매우 불쾌한보기의 결과를 비교하기 위해이 글을 작성했습니다. 테이블의 데이터와 테이블 자체의 스키마에 차이가있는 임시 테이블 쌍 #DataVariances 및 #SchemaVariances를 만듭니다.
두 테이블에 모두 기본 키가 있어야하지만 원본 테이블에 기본 키가없는 경우 ID 열을 사용하여 tempdb에 놓을 수 있습니다.
declare @TableA_ThreePartName nvarchar(max) = ''
declare @TableB_ThreePartName nvarchar(max) = ''
declare @KeyName nvarchar(max) = ''
/***********************************************************************************************
Script to compare two tables and return differneces in schema and data.
Author: Devin Lamothe 2017-08-11
***********************************************************************************************/
set nocount on
-- Split three part name into database/schema/table
declare @Database_A nvarchar(max) = (
select left(@TableA_ThreePartName,charindex('.',@TableA_ThreePartName) - 1))
declare @Table_A nvarchar(max) = (
select right(@TableA_ThreePartName,len(@TableA_ThreePartName) - charindex('.',@TableA_ThreePartName,len(@Database_A) + 2)))
declare @Schema_A nvarchar(max) = (
select replace(replace(@TableA_ThreePartName,@Database_A + '.',''),'.' + @Table_A,''))
declare @Database_B nvarchar(max) = (
select left(@TableB_ThreePartName,charindex('.',@TableB_ThreePartName) - 1))
declare @Table_B nvarchar(max) = (
select right(@TableB_ThreePartName,len(@TableB_ThreePartName) - charindex('.',@TableB_ThreePartName,len(@Database_B) + 2)))
declare @Schema_B nvarchar(max) = (
select replace(replace(@TableB_ThreePartName,@Database_B + '.',''),'.' + @Table_B,''))
-- Get schema for both tables
declare @GetTableADetails nvarchar(max) = '
use [' + @Database_A +']
select COLUMN_NAME
, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ''' + @Table_A + '''
and TABLE_SCHEMA = ''' + @Schema_A + '''
'
create table #Table_A_Details (
ColumnName nvarchar(max)
, DataType nvarchar(max)
)
insert into #Table_A_Details
exec (@GetTableADetails)
declare @GetTableBDetails nvarchar(max) = '
use [' + @Database_B +']
select COLUMN_NAME
, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ''' + @Table_B + '''
and TABLE_SCHEMA = ''' + @Schema_B + '''
'
create table #Table_B_Details (
ColumnName nvarchar(max)
, DataType nvarchar(max)
)
insert into #Table_B_Details
exec (@GetTableBDetails)
-- Get differences in table schema
select ROW_NUMBER() over (order by
a.ColumnName
, b.ColumnName) as RowKey
, a.ColumnName as A_ColumnName
, a.DataType as A_DataType
, b.ColumnName as B_ColumnName
, b.DataType as B_DataType
into #FieldList
from #Table_A_Details a
full outer join #Table_B_Details b
on a.ColumnName = b.ColumnName
where a.ColumnName is null
or b.ColumnName is null
or a.DataType <> b.DataType
drop table #Table_A_Details
drop table #Table_B_Details
select coalesce(A_ColumnName,B_ColumnName) as ColumnName
, A_DataType
, B_DataType
into #SchemaVariances
from #FieldList
-- Get differences in table data
declare @LastColumn int = (select max(RowKey) from #FieldList)
declare @RowNumber int = 1
declare @ThisField nvarchar(max)
declare @TestSql nvarchar(max)
create table #DataVariances (
TableKey nvarchar(max)
, FieldName nvarchar(max)
, TableA_Value nvarchar(max)
, TableB_Value nvarchar(max)
)
delete from #FieldList where A_DataType in ('varbinary','image') or B_DataType in ('varbinary','image')
while @RowNumber <= @LastColumn begin
set @TestSql = '
select coalesce(a.[' + @KeyName + '],b.[' + @KeyName + ']) as TableKey
, ''' + @ThisField + ''' as FieldName
, a.[' + @ThisField + '] as [TableA_Value]
, b.[' + @ThisField + '] as [TableB_Value]
from [' + @Database_A + '].[' + @Schema_A + '].[' + @Table_A + '] a
inner join [' + @Database_B + '].[' + @Schema_B + '].[' + @Table_B + '] b
on a.[' + @KeyName + '] = b.[' + @KeyName + ']
where ltrim(rtrim(a.[' + @ThisField + '])) <> ltrim(rtrim(b.[' + @ThisField + ']))
or (a.[' + @ThisField + '] is null and b.[' + @ThisField + '] is not null)
or (a.[' + @ThisField + '] is not null and b.[' + @ThisField + '] is null)
'
insert into #DataVariances
exec (@TestSql)
set @RowNumber = @RowNumber + 1
set @ThisField = (select coalesce(A_ColumnName,B_ColumnName) from #FieldList a where RowKey = @RowNumber)
end
drop table #FieldList
print 'Query complete. Select from #DataVariances to verify data integrity or #SchemaVariances to verify schemas match. Data types varbinary and image are not checked.'
대부분의 응답은 카밀이 제기 한 문제를 무시하는 것 같습니다. (여기서 테이블은 동일한 행을 포함하지만 각 테이블에서 다른 행이 반복됩니다.) 불행히도 저는 Oracle에 있기 때문에 그의 솔루션을 사용할 수 없습니다. 내가 생각 해낼 수 있었던 최고는 :
SELECT * FROM
(
SELECT column1, column2, ..., COUNT(*) AS the_count
FROM tableA
GROUP BY column1, column2, ...
MINUS
SELECT column1, column2, ..., COUNT(*) AS the_count
FROM tableB
GROUP BY column1, column2, ...
)
UNION ALL
(
SELECT column1, column2, ..., COUNT(*) AS the_count
FROM tableB
GROUP BY column1, column2, ...
MINUS
SELECT column1, column2, ..., COUNT(*) AS the_count
FROM tableA
GROUP BY column1, column2, ...
)
아래의 간단한 쿼리를 사용하여 DB2 테이블 두 테이블의 데이터를 비교할 수 있습니다.
1 단계 : 스키마 (S)의 테이블 (T1)에서 비교할 모든 열을 선택합니다.
SELECT T1.col1,T1.col3,T1.col5 from S.T1
Step 2:- Use 'Minus' keyword for comparing 2 tables.
Step 3:- Select which all columns we need to compare from table (T2) of schema(S)
SELECT T2.col1,T2.col3,T2.col5 from S.T1
END result:- SELECT T1.col1,T1.col3,T1.col5 from S.T1 MINUS SELECT T2.col1,T2.col3,T2.col5 from S.T1;
If the query returns no rows then the data is exactly the same.
To compare T1(PK, A, B) and T2(PK, A, B).
First compare primary key sets to look for missing key values on eiter side:
SELECT T1.*, T2.* FROM T1 FULL OUTER JOIN T2 ON T1.PK=T2.PK WHERE T1.PK IS NULL OR T2.PK IS NULL;
Then list all value mismatch:
SELECT T1.PK, 'A' AS columnName, T1.A AS leftValue, T2.A AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE T1.A != T2.A
UNION ALL
SELECT T1.PK, 'B' AS columnName, T1.B AS leftValue, T2.B AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE T1.B != T2.B
A and B must be of same type. You can use INFORMATION SCHEMA to generate the SELECT.
For example for columns of type varchar:
SELECT concat('SELECT T1.PK, ''', COLUMN_NAME, ''' AS columnName, T1.', COLUMN_NAME, ' AS leftValue, T2.', COLUMN_NAME, ' AS rightValue FROM T1 JOIN T2 ON T1.PK=T2.PK WHERE T1.',COLUMN_NAME, '!=T2.', COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='T1' AND DATA_TYPE IN ('nvarchar','varchar');
In MySQL, where "minus" is not supported, and taking performance into account, this is a fast
query:
SELECT
t1.id,
t1.id
FROM t1 inner join t2 using (id) where concat(t1.C, t1.D, ...)<>concat(t2.C, t2.D, ...)
An alternative, enhanced query based on answer by dietbuddha & IanMc. The query includes description to helpfully show where rows exist and are missing. (NB: for SQL Server)
(
select 'InTableA_NoMatchInTableB' as Msg, * from tableA
except
select 'InTableA_NoMatchInTableB' , * from tableB
)
union all
(
select 'InTableB_NoMatchInTableA' as Msg, * from tableB
except
select 'InTableB_NNoMatchInTableA' ,* from tableA
)
SELECT *
FROM TABLE A
WHERE NOT EXISTS (SELECT 'X'
FROM TABLE B
WHERE B.KEYFIELD1 = A.KEYFIELD1
AND B.KEYFIELD2 = A.KEYFIELD2
AND B.KEYFIELD3 = A.KEYFIELD3)
;
'X' is any value.
Switch the tables to see the different discrepancies.
Make sure to join the key fields in your tables.
Or just use the MINUS operator with 2 select statements, however, MINUS can only work in Oracle.
참고URL : https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables
'Program Club' 카테고리의 다른 글
| 'init (start : end :)'는 더 이상 사용되지 않습니다. Swift 3에서 제거됩니다. '.. <'연산자를 사용합니다. (0) | 2020.10.26 |
|---|---|
| 어설 션 vs. JUnit 어설 션 (0) | 2020.10.26 |
| JavaScript를 사용하여 문자열에서 쉼표 제거 (0) | 2020.10.26 |
| mySQL 선택 범위 (0) | 2020.10.26 |
| AngularJS-선택을위한 값 속성 (0) | 2020.10.26 |