Operador UNION e UNION ALL - união de queries

Operador UNION
união de query's, juntar, unir, atrelar, conectar, ligar, acoplar, vincular... várias queries em um único resultado, juntando o select num moido só...


Descrição:
Este operador possibilita a combinação de uma ou mais queries em um único resultado consistindo todas as linhas pertencentes a todas as querys neste união. Veja que isto é diferente de usar JOIN'sque combinam colunas de diferentes tabelas

Regras:
Duas regras básicas para o uso do UNION
- O número e a ordem das colunas deve ser identico em todas as queries
- O tipo de dados deve ser compativel, caso náo for, uma dica seria converter tudo pra varchar usando a função convert

Opções:
Temos a opção de definir a união total com UNION ALL ou somente a união simples com UNION, vejamos abaixo as diferenças:
- UNION: faz a união e já elimina linhas idênticas
- UNION ALL: faz a união e mantém as linhas idênticas
No exemplo abaixo veremos melhor esta a diferença.

Exemplo:
Vamos fazer a união das tabelas clientes e fornecedores, somente com o campo 'Nome', note que o registro "Vito Corleone" (ele mesmo Don Vito, o poderoso Chefão), aparece na tabela de clientes e fornecedores, logo em UNION ele vai aparecer uma única vez e em UNION ALL ele vai aparecer duas vezes.

tabelas para testes

--configurando para não aparecer o contador de registros executados 
set nocount on
--declarando tabelas temporarias para teste
declare @tbClientes table (cod int identity, nome varchar(30))
declare @tbFornecedores table (cod int identity, nome varchar(30))
                  
--inserindo dados na tabela clientes
insert into @tbClientes (nome) values ('Vito Corleone')
insert into @tbClientes (nome) values ('Bonasera')
insert into @tbClientes (nome) values ('Luca Brasi')
                  
--inserindo dados na tabela fornecedores
insert into @tbFornecedores (nome) values ('Paul Vitti')
insert into @tbFornecedores (nome) values ('Tonny Gordo')
insert into @tbFornecedores (nome) values ('Vito Corleone')

.
.
.
.
UNION

--executando o UNION 
select nome from @tbClientes
union
select nome from @tbFornecedores
order by nome

Resultado, observe que Vito Corleone aparece apenas uma única vez:

nome
------------------------------
Bonasera
Luca Brasi
Paul Vitti
Tonny Gordo
Vito Corleone

.
.
.
.
UNION ALL

--executando o UNION ALL
select nome from @tbClientes
union all
select nome from @tbFornecedores
order by nome

Resultado, observe que Vito Corleone aparece duas vezes:

nome
------------------------------
Bonasera
Luca Brasi
Paul Vitti
Tonny Gordo
Vito Corleone
Vito Corleone

OBS:
- Para a ordenação o comando ORDER BY tem que ficar após o último SELECT
- No exemplo citado para as construções UNION foram usadas duas query's, mas poderiamos ter usado N query's


AUTOR: "eriva_br"
Dúvidas, criticas, contribuições, correções e adições seram bem vindas. 

Código para encontrar / descobrir último dia do mês

Código para achar / localizar o último dia do mês em SQL Server

DECLARE @mes CHAR(2), @ano CHAR(4)
        SET @mes = '02'
        SET @ano = '2008'
        SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01'))))
        
        
        SET @mes = '02'
        SET @ano = '2007'
        SELECT DAY(DATEADD(d,-1,DATEADD(M,1,CONVERT(DATETIME,@ano + @mes + '01'))))

funções utilizadas:
DAY
DATEADD
CONVERT


AUTOR: "eriva_br"
Dúvidas, criticas, contribuições, correções e adições seram bem vindas. 

xp_cmdshell - comandos no SQL Server via shell

Caracteristicas:
Nome: XP_CMDSHELL
Localização: Banco de dados Master
Tipo: Extended Procedure
Descrição: Esta procedure executa uma sequencia de comandos em string do tipo shell no computador onde o sql server está instalado.
Utilidades: listar diretórios; criar, copiar, apagar arquivos; enviar mensagens, etc

Parâmetros:
'command_string', obrigatório, é o comando a ser executado
no_output, opcional, se colocado indica que não será retornado mensagem de saída na execução

Retornos:
retona 0 ou 1: 0-sucesso ou 1-Falha

Observações:
Obs1.: esta proc trabalha de forma sincrona, os comandos colocados abaixo dela só serão executados após o término de sua execução, ao contrário do comando shell do vb6 que trabalha de forma assincrona, ao ser executado, mesmo sem ter finalizado o comando já é liberado para a linha subsequente.
Obs2.: verificar segurança, tomar cuidados com SQL Injection, dica do nosso amigo Micox
http://www.macoratti.net/sql_inj.htm
http://www.google.com.br/search?hl=pt-BR&a...ta=lr%3Dlang_pt

Exemplos de uso:
exemplo 1: copiando um arquivo

declare @retorno INT
  EXEC @retorno = MASTER..xp_cmdshell 'copy C:\Lixo\teste.txt C:\Lixo\testeBKP.txt'
  select CASE WHEN @retorno = 0 THEN 'COPIADO COM SUCESSO' ELSE 'FALHA NO COMANDO' END as Retorno

exemplo 2: listando os arquivos de uma pasta

declare @retorno int
  EXEC @retorno = master..xp_cmdshell 'dir C:\lixo\'
  if @retorno = 0
          print 'comando executado com EXITO'
  else
          print 'FALHA no comando'

para testar esse exemplo verificando uma execução com sucesso e uma com falha, teste passando o caminho correto do arquivo e depois teste definindo o caminho incorretamente


exemplo 3: deletando um arquivo

EXEC MASTER..XP_CMDSHELL 'del C:\Lixo\teste.txt'

exemplo 4: enviando mensagens

EXEC master..xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server será desligado em em 1 minuto.'

exemplo 5: escrevendo em um arquivo texto

DECLARE @arqTexto VARCHAR(8000),
          @caminho VARCHAR(255),
                  @retEcho INT --variavel para verificar se o comando foi executado com exito ou ocorreu alguma falha
  
          SET @caminho = 'C:\Lixo\teste.txt'
  
          SET @arqTexto = 'ECHO Registro;CAMPO1;CAMPO2;CAMPO3 ' + CONVERT(VARCHAR(30),GETDATE()) +  ' >> ' + @caminho
  
          --remove caracteres não aceitos pelo comando ECHO, veja no final do post comentário
          select @arqTexto = dbo.FU_CONVERTE_TXT(@arqTexto)
  
          --escreve no arquivo txt, se ele naum existir será criado, se existir será adicionado (incrementado) a partir da última linha
          EXEC @retEcho = MASTER..XP_CMDSHELL @arqTexto, NO_OUTPUT --NO_OUTPUT indica que o comando não irá retornar alguma possível mensagem na sua execução
          IF (@retEcho <> 0)
          BEGIN
                  --caso ocorrer algum erro podemos retornar uma mensagem
                  SELECT 'ocorreu erro, verifique se o diretório existe' as Falha, @retEcho as retEcho
          END

exemplo 6: escrevendo arquivo texto, vamos escrever um arquivo texto com alguns campos da tabela authors do banco de dados pubs, através de um loop

--definindo o banco pubs
  use pubs;
  --definindo variaveis
  declare @arqTexto varchar(8000),
          @au_fname varchar(50),
          @au_lname varchar(50),
          @caminho varchar(255),
          @au_id varchar(20),
          @retEcho INT --variavel para verificar se o comando foi executado com exito ou ocorreu alguma falha
  
  --definindo o caminho e arquivo que será escrito, OBS: este caminho deve ser no servidor onde estiver instalado o SQL Server
  set @caminho = 'C:\Lixo\teste.txt'
  --setando o menor id para verificação do loop
  select @au_id = min(au_id) from authors
  --while da tabela authors
  while @au_id is not null
  begin
          --buscando os valores dos campos e adicionando nas variaveis
          select @au_id = au_id, @au_lname = au_lname, @au_fname = au_fname from authors
          where au_id = @au_id
          --setando o menor id para verificação do loop
          select @au_id = min(au_id) from authors
          where au_id > @au_id
          --definindo a linha que será escrita no arquivo txt
          SET @arqTexto = 'ECHO Registro;' + @au_id + ';' + @au_lname + ';' + @au_fname +  ' >> ' + @caminho
          --escreve no arquivo txt, se ele naum existir será criado, se existir será adicionado (incrementado) a partir da última linha
          exec @retEcho = MASTER..XP_CMDSHELL @arqTexto
          IF (@retEcho <> 0)
          BEGIN
                  --caso ocorrer algum erro podemos retornar uma mensagem
                  select 'ocorreu erro, verifique se o diretório existe' as Falha, @retEcho as retEcho
                  --parar o loop em caso de erro
                  break
          END
  end

Atenção:
Para escrever arquivo texto, cuidado com os caracteres especiais, pois se for um destes caracteres no comando a linha inteira não é escrita, dai imagina um arquivo texto de exportação faltando uma linha lá no meio do arquivo... como diria o Silvio Luiz, "o que que eu vou dizer lá no 'escritório'!"
Os caracteres que verifiquei até agora que não são aceitos pelo comando ECHO estão na função abaixo que fiz para trocar tais caracteres, se ver mais algum por favor nos avise:

CREATE FUNCTION FU_TRANSFORMA_CARACTERES (@Texto VARCHAR(8000))
  RETURNS VARCHAR(8000)
  AS
  BEGIN
  SET @Texto = UPPER(REPLACE(@Texto,'Á','A'))
  SET @Texto = UPPER(REPLACE(@Texto,'Á','A'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ã','A'))
  SET @Texto = UPPER(REPLACE(@Texto,'Â','A'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ó','O'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ô','O'))
  SET @Texto = UPPER(REPLACE(@Texto,'Õ','O'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ú','U'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ü','U'))
  SET @Texto = UPPER(REPLACE(@Texto,'Í','I'))
  SET @Texto = UPPER(REPLACE(@Texto,'É','E'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ê','E'))
  SET @Texto = UPPER(REPLACE(@Texto,'Ç','C'))
  SET @Texto = REPLACE(@Texto,'&','e')
  SET @Texto = REPLACE(@Texto,'"',' ')
  
  RETURN @Texto
  END

AUTOR: "eriva_br"

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.