Archives

T-SQL – Mesclando dados

O SQL Server 2008 trouxe para o T-SQL um comando brilhante que realmente facilita a vida de muita gente; o comando em questão é o MERGE. A idéia deste comando é mesclar dados, logo você pode comparar duas tabelas e utilizar o comando MERGE para sincronizá-las.

Vamos a um exemplo simples:

Primeiro criamos duas tabelas com estrutura idêntica. A chave primária destas tabelas é o campo CPF:

create table Tabela1 (
Nome varchar(50),
Idade int,
CPF char(11) primary key);

create table Tabela2 (
Nome varchar(50),
Idade int,
CPF char(11) primary key);

Agora inserimos dados nas tabelas. Observe que os dados estão diferentes, alguns tem diferença no nome, outros registros estão sobrando ou faltando em ambas tabelas:

insert into Tabela1 values
('Silas Mendes' , 29, '08148338716'),
('Maria José'   , 49, '55978913269'),
('Pedro Ribeiro', 53, '05899714732'),
('Joana Silva'  , 18, '74105689623');

insert into Tabela2 values
('SILAS Mendes'       , 29, '08148338716'),
('Maria Jose'         , 49, '55978913269'),
('Pedro Ribeiro Souza', 53, '05899714732'),
('Carlos Mania'       , 19, '12345678999');

Agora precisamos sincronizar as tabelas, vamos analisar passo-a-passo a construção do comando MERGE (o comando completo está disponível no fim do texto) .

A tabela de destino (target) é a tabela2; a tabela de origem (source) é a tabela1. A chave primária das tabelas será utilizada na comparação:

merge into tabela2 as target
using (select nome, idade, cpf from tabela1)
  as source (nome, idade, cpf)
  on (target.cpf = source.cpf)

Quando os números dos CPFs forem encontrados em ambas tabelas então atualizamos os dados na tabela alvo (target):

when matched then
		 update set target.nome = source.nome,
					target.idade = source.idade,
					target.cpf = source.cpf

Quando os números dos CPFs não forem encontrados na tabela de destino, então inserimos os dados da tabela de origem:

when not matched by target then
		 insert (nome, idade, cpf) values (nome, idade, cpf)

E finalmente, quando existirem números de CPFs no destino que não existem na origem, apagamos os registros que sobram:

when not matched by source then
		 delete;

Veja o código completo do comando MERGE:

merge into tabela2 as target
using (select nome, idade, cpf from tabela1)
  as source (nome, idade, cpf)
  on (target.cpf = source.cpf)

-- Atualiza registros diferentes
when matched then
		 update set target.nome = source.nome,
					target.idade = source.idade,
					target.cpf = source.cpf

-- Insere registros que não existem no destino
when not matched by target then
		 insert (nome, idade, cpf) values (nome, idade, cpf)

-- Se existir no destino e não existir na origem é apagado
when not matched by source then
		 delete;

Por fim realizamos o SELECT nas duas tabelas para verificar o resultado após a sincronização:


select * from Tabela1;
select * from Tabela2;

Bom trabalho, bom estudo!

Comparando text / ntext

 

No SQL Server 2005 temos os novos campos do tipo VAR…(MAX) que vieram aliviar o trabalho de muita gente. Um dos problemas mais comuns na versão anterior (2000) é quando precisamos comparar dados de campos do tipo text ou ntext, aí nos deparamos com um erro do tipo:

Server: Msg 306, Level 16, State 1, Line 1

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Eu já vivi essa situação algumas vezes e deixo aqui a forma como tentei resolver (Se tiverem outras sugestões fiquem a vontade para expor, ok?).

(Não fiz testes de perfomance nessa solução, o foco está somente em comparar as colunas tipo text / ntext.)

Imagine que eu tenha duas tabelas:

CREATE TABLE #tb_msg_tela
(id INT IDENTITY(1,1), texto TEXT)

GO

CREATE TABLE #tb_msg_impressao
(id INT IDENTITY(1,1), texto TEXT)

Com os seguintes dados:

INSERT INTO #tb_msg_tela VALUES (NULL)
INSERT INTO #tb_msg_tela VALUES (‘Campo text’)
INSERT INTO #tb_msg_tela VALUES (‘Teste comparação‘)
INSERT INTO #tb_msg_tela VALUES (‘Se caísse para o exterior, para o limite do universo, encontraria uma perto e pôsteres que indicassem BECO SEM SAÍDA?’)

INSERT INTO #tb_msg_impressao VALUES (”)
INSERT INTO #tb_msg_impressao VALUES (‘Campo text’)
INSERT INTO #tb_msg_impressao VALUES (‘Teste comparacao‘)
INSERT INTO #tb_msg_impressao VALUES (‘Se caisse para o esterior, p/ o limite do universo, encontraria uma perto e pôsteres que indicassem BECO SEM SAÍDA?’)

Observe que existem diferenças em alguns textos (em vermelho).

Para realizar o relacionamento das duas tabelas e encontrar os campos diferentes não podemos simplesmente utilizar:

SELECT * FROM #tb_msg_tela a, #tb_msg_impressao b
WHERE a.id = b.id AND a.texto <> b.texto

Essa consulta retornará um erro porque estamos comparando os campos text utilizando o <>.

Então o primeiro passo é encontrar o maior texto nessa coluna, para isso podemos usar as funções DATALENGHT e MAX:

SELECT MAX(DATALENGTH(texto)) FROM #tb_msg_tela
SELECT MAX(DATALENGTH(texto)) FROM #tb_msg_impressao

O resultado será:

———–

658

———–

656

Então sabemos que o maior texto dessa coluna não ultrapassa 700 caracteres, logo, podemos utilizar esse número como apoio no próximo passo, onde utilizaremos a função SUBSTRING:

SELECT
                *
FROM  
                #tb_msg_tela a,
                #tb_msg_impressao b
WHERE
                a.id = b.id
                AND ISNULL(SUBSTRING(a.texto, 0, 700),”) <> ISNULL(SUBSTRING(b.texto, 0, 700),”)

 

A função ISNULL é importante pois sem ela os campos Nulos serão ignorados.

Veja que a consulta só ira retornar os campos com as diferenças.

É um processo simples, mas que pode dar dor de cabeça por conta das limitações do tipo de dados. Pra quem ta iniciando o desenvolvimento utilizando o SQL Server 2005 a recomendação é: substitua os datatypes ntext, text, image por nvarchar(Max), varchar(Max) e varbinary(Max). Além de outras vantagens, com os novos datatypes não existem as antigas diferenças entre varchar e text.