Como comparar dados entre duas tabelas em diferentes bases de dados usando o SQL Server 2008?
Tenho duas bases de dados, chamadas DB1 e DB2 no servidor Sql 2008. Estas duas bases de dados têm as mesmas tabelas e os mesmos dados de tabela também. No entanto, eu quero verificar se há alguma diferença entre os dados nestas tabelas.
Alguém me pode ajudar com um guião para isto?9 answers
select *
from (
select *
from DB1.dbo.Table
except
select *
from DB2.dbo.Table
) as T
union all
select *
from (
select *
from DB2.dbo.Table
except
select *
from DB1.dbo.Table
) as T
Código de ensaio:
declare @T1 table (ID int)
declare @T2 table (ID int)
insert into @T1 values(1),(2)
insert into @T2 values(2),(3)
select *
from (
select *
from @T1
except
select *
from @T2
) as T
union all
select *
from (
select *
from @T2
except
select *
from @T1
) as T
Resultado:
ID
-----------
1
3
Reason-these tools save a lot of time and make the process less error prone.
Eu usei ferramentas de comparação do ApexSQL (diferenças e diferenças de dados) mas você não pode correr mal com outras ferramentas marc_s e Marina Nastenko já apontou.
Se TEM a certeza absoluta de que só vai comparar tabelas uma vez, então SQL está bem. mas se vais precisar disto de vez em quando, vais ficar melhor com uma ferramenta de terceiros.
Se você não tem orçamento para comprá-lo, em seguida, basta usá-lo no modo de teste para obter o trabalho feito.
Espero que os novos leitores achem isto útil, apesar de ser uma resposta tardia...Eu fiz coisas como esta usando a função Checksum (*)
No essencial, ele cria um código de validação de nível de linha em todos os dados das colunas, você poderia então comparar o código de validação de cada linha para cada tabela um para o outro, usar uma junção à esquerda, para encontrar linhas que são diferentes.
Espero que tenha feito sentido... Melhor com um exemplo....select *
from
( select checksum(*) as chk, userid as k from UserAccounts) as t1
left join
( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
where t1.chk <> t2.chk
Comparando as duas bases de dados na Base de dados SQL. Tente esta consulta que pode ajudar.
SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS
system_data_type FROM [***Database Name 1***].sys.[tables] AS T
INNER JOIN [***Database Name 1***].sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN [***Database Name 1***].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
EXCEPT SELECT T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS system_data_type FROM ***Database Name 2***.sys.[tables] AS T
INNER JOIN ***Database Name 2***.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN ***Database Name 2***.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
select * from DB1.dbo.Table a inner join DB2.dbo.Table b on b.PrimKey = a.PrimKey
where a.FirstColumn <> b.FirstColumn ...
O código de validação que o Matt recomendou é provavelmente uma abordagem melhor para comparar colunas em vez de comparar cada coluna
Se a base de dados estiver no mesmo servidor use {[[0]} o formato ao aceder a uma tabela que reside numa base de dados diferente.
Eg: [DB1].[dbo].[TableName]
Se as bases de dados em diferentes servidores olharem para criando servidores ligados (motor de bases de Dados SQL Server)
Outra solução(não T-SQL
): pode usar o utilitário tablediff .
Por exemplo, se quiser comparar duas tabelas (Localitate
) de dois servidores diferentes (ROBUH01 & ROBUH02), poderá usar este comando shell:
C:\Program Files\Microsoft SQL Server\100\COM>tablediff -sourceserver ROBUH01 -s
ourcedatabase SIM01 -sourceschema dbo -sourcetable Localitate -destinationserver
ROBUH02 -destinationschema dbo -destinationdatabase SIM02 -destinationtable Lo
calitate
Resultados:
Microsoft (R) SQL Server Replication Diff Tool Copyright (c) 2008 Microsoft Corporation User-specified agent parameter values:
-sourceserver ROBUH01
-sourcedatabase SIM01
-sourceschema dbo
-sourcetable Localitate
-destinationserver ROBUH02
-destinationschema dbo
-destinationdatabase SIM02
-destinationtable Localitate
Table [SIM01].[dbo].[Localitate] on ROBUH01 and Table [SIM02].[dbo].[Localitate ] on ROBUH02 have 10 differences.
Err Id Dest.
Only 21433 Dest.
Only 21434 Dest.
Only 21435 Dest.
Only 21436 Dest.
Only 21437 Dest.
Only 21438 Dest.
Only 21439 Dest.
Only 21441 Dest.
Only 21442 Dest.
Only 21443
The requested operation took 9,9472657 seconds.
------------------------------------------------------------------------
Se ambos estiverem no mesmo servidor. Você pode verificar tabelas semelhantes usando a seguinte Pesquisa:
select
fdb.name, sdb.name
from
FIRSTDBNAME.sys.tables fdb
join SECONDDBNAME.sys.tables sdb
on fdb.name = sdb.name -- compare same name tables
order by
1
Ao listar uma tabela semelhante, poderá comparar o esquema das colunas usando sys.columns
a vista.
EXEC master.dbo.CompareTables 'DB1', 'dbo', 'table1', 'DB2', 'dbo', 'table2'
Se quiser comparar duas bases de dados, utilize o procedimento 'CompareDatabases'. Exemplo:
EXEC master.dbo.CompareDatabases 'DB1', 'DB2'
Nota: - tentei tornar os procedimentos seguros, mas de qualquer forma, esses procedimentos são apenas para testes e depuração.
- Se você quer uma solução completa para comparação use terceiros como (Visual Studio, ...)
USE [master]
GO
create proc [dbo].[CompareDatabases]
@FirstDatabaseName nvarchar(50),
@SecondDatabaseName nvarchar(50)
as
begin
-- Check that databases exist
if not exists(SELECT name FROM sys.databases WHERE name=@FirstDatabaseName)
return 0
if not exists(SELECT name FROM sys.databases WHERE name=@SecondDatabaseName)
return 0
declare @result table (TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('(Select distinct TABLE_NAME from ' + @FirstDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')'
+ 'intersect'
+ '(Select distinct TABLE_NAME from ' + @SecondDatabaseName + '.INFORMATION_SCHEMA.COLUMNS '
+'Where TABLE_SCHEMA=''dbo'')')
DECLARE @TABLE_NAME nvarchar(256)
DECLARE curseur CURSOR FOR
SELECT TABLE_NAME FROM @result
OPEN curseur
FETCH curseur INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
print 'TABLE : ' + @TABLE_NAME
EXEC master.dbo.CompareTables @FirstDatabaseName, 'dbo', @TABLE_NAME, @SecondDatabaseName, 'dbo', @TABLE_NAME
FETCH curseur INTO @TABLE_NAME
END
CLOSE curseur
DEALLOCATE curseur
SET NOCOUNT OFF
end
GO
.
USE [master]
GO
CREATE PROC [dbo].[CompareTables]
@FirstTABLE_CATALOG nvarchar(256),
@FirstTABLE_SCHEMA nvarchar(256),
@FirstTABLE_NAME nvarchar(256),
@SecondTABLE_CATALOG nvarchar(256),
@SecondTABLE_SCHEMA nvarchar(256),
@SecondTABLE_NAME nvarchar(256)
AS
BEGIN
-- Verify if first table exist
DECLARE @table1 nvarchar(256) = @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME
DECLARE @return_status int
EXEC @return_status = master.dbo.TableExist @FirstTABLE_CATALOG, @FirstTABLE_SCHEMA, @FirstTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table1 + ' : Table Not FOUND'
RETURN 0
END
-- Verify if second table exist
DECLARE @table2 nvarchar(256) = @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME
EXEC @return_status = master.dbo.TableExist @SecondTABLE_CATALOG, @SecondTABLE_SCHEMA, @SecondTABLE_NAME
IF @return_status = 0
BEGIN
PRINT @table2 + ' : Table Not FOUND'
RETURN 0
END
-- Compare the two tables
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = '('
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table1 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ ')'
+ 'UNION'
+ '('
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @SecondTABLE_CATALOG + '.' + @SecondTABLE_SCHEMA + '.' + @SecondTABLE_NAME + ')'
+ 'EXCEPT'
+ '(SELECT ''' + @table2 + ''' as _Table, * FROM ' + @FirstTABLE_CATALOG + '.' + @FirstTABLE_SCHEMA + '.' + @FirstTABLE_NAME + ')'
+ ')'
DECLARE @wrapper AS NVARCHAR(MAX) = 'if exists (' + @sql + ')' + char(10) + ' (' + @sql + ')ORDER BY 2'
Exec(@wrapper)
END
GO
.
USE [master]
GO
CREATE PROC [dbo].[TableExist]
@TABLE_CATALOG nvarchar(256),
@TABLE_SCHEMA nvarchar(256),
@TABLE_NAME nvarchar(256)
AS
BEGIN
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=@TABLE_CATALOG)
RETURN 0
declare @result table (TABLE_SCHEMA nvarchar(256), TABLE_NAME nvarchar(256))
SET NOCOUNT ON
insert into @result EXEC('Select TABLE_SCHEMA, TABLE_NAME from ' + @TABLE_CATALOG + '.INFORMATION_SCHEMA.COLUMNS')
SET NOCOUNT OFF
IF EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME FROM @result
WHERE TABLE_SCHEMA=@TABLE_SCHEMA AND TABLE_NAME=@TABLE_NAME)
RETURN 1
RETURN 0
END
GO