Arquivo
T-SQL: Importar dados do Excel e Access usando MDAC – ACE
O Microsoft SQL Server possue ferramentas para fazer o trabalho de importação e exportação de dados do Excel, Access entre outras fontes.
Porém, no meu caso desejo executar um script importação e/ou exportação.
Como fazer isso usando T-SQL?
Para isso é necessário ter instalado o Provider no servidor do SQL Server.
- Microsoft.Jet.OLEDB.4.0 – depreciado
- Microsoft.ACE.OLEDB.12.0
- Microsoft.ACE.OLEDB.16.0
O Jet OLEDB foi depreciado, mas existem diversos códigos antigos de exemplos com ele. Podemos aproveitar esses códigos, substituindo pelo Microsft ACE OLEDB.
Office 2010 – ACE 12 – Microsoft Access Database Engine 2010 Redistributable
Office 2016 – ACE 16 – Microsoft Access Database Engine 2016 Redistributable
Para conferir a instalação entre no SQL Managment Studio – Server Objects – Linked Servers – Providers
-- Ativa Linked Server
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
-- Ativa Permissão ao Provider versão ACE 12
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess',1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters',1
GO
Caso recebe algumas mensagem referente a permissão de acesso, ou erro de impossibilidade de criar linked server, leia Permissions needed to set up linked server with out-of-process provider
-- Ativa Permissão ao Provider versão ACE 16
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess',1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters',1
GO
-- Usa o Provider Jet ( Depeciado ) para acessar XLS
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Excel\File.xls; Extended Properties=Excel 5.0')...[Sheet1$]
GO
-- Usa o Provider ACE 12 para acessar XLS / XLSX
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\ExcelFiles\File2003.xls; Extended Properties=Excel 8.0')...[Sheet1$]
GO
-- Usa o Provider ACE 16 para acessar XLS / XLSX
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=C:\ExcelFiles\File2016.xlsx; Extended Properties=Excel 12.0')...[Sheet1$]
GO
Resultado
Importante!!!
O arquivo está na unidade “C:” local do SERVIDOR SQL Server.
Para abrir arquivo da rede, use o caminho COMPLETO, conforme abaixo:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 8.0')...[NomePlanilha$]SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 12.0')...[NomePlanilha$]fonte:
Query Excel file source through Linked Server
How to use Excel with SQL Server linked servers and distributed queries
Configuiring a Linked Microsoft Access Server on SQL 2005
Microsoft.Jet.OLEDB4.0 – 32bit and 64bit ( Deprecated )
Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
Cannot create instance of “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” on x64 Vista with x64 SQL Server 2008 – sp_MSSet_oledb_prop
How to Import and Export SQL Server data to an Excel file
T-SQL OpenDataSource on SQL Server 2017
Import data from Excel to SQL Server or Azure SQL Database
Permissions needed to set up linked server with out-of-process provider
SQL Server – Gerar um número randômico
Preciso gerar um volume de dados com valor inteiro e/ou decimal, para popular uma tabela no SQL Server. Não quero depender de ferramenta de terceiro para gerar os dados.
A solução foi fazer um script Gerar_Numero_Randomico.sql, que está disponível no meu GitHub.
Explicação dos comandos usados, abaixo:
1. Gerar um número randômico, ou seja, número aleatório. Exemplo: 1, 524, 6421, 60544, 40.
Para isso usarei a função NEWID() que cria um valor hexadecinal único do tipo uniqueidentifier.
SELECT NEWID() AS ID_Hex
2. Extrair o valor inteiro do ID_Hex, usando a função CHECKSUM().
SELECT
A.ID_Hex,
CHECKSUM(A.ID_Hex) AS Nr_Randomico,
FROM ( — Nr_Randomico baseado em um ID Hexadecimal
SELECT NEWID() AS ID_Hex
) A
3. Criar o valor decimal, com 4 casas, dividindo o valor inteiro por 10000,0000 e convertendo o resultado da divisão usando a função CAST().
SELECT
A.ID_Hex,
CHECKSUM(A.ID_Hex) AS Nr_Randomico,
CAST(CHECKSUM(ID_Hex) / 10000.0000 AS decimal(10,4)) AS Nr_Randomico_Decimal
FROM ( — Nr_Randomico baseado em um ID Hexadecimal
SELECT NEWID() AS ID_Hex
) A
Com os três passos, obtemos o seguinte resultado:
| ID_Hex | Nr_Randomico | Nr_Randomico_Decimal |
| 4CBFDEC3-F57C-433D-B145-7959B570FBC0 | -1216800284 | -121680.0284 |
Podemos gerar novos valores inteiros e decimais, usando parte do Nr_Randomico_Decimal. Exemplo:
| Nr_Randomico_Decimal | Novos Valores | Descrição do Valor |
| -121680.0284 | -121680 | Somente parte inteira |
| -121680.0284 | 0284 | Somente parte decimal |
| -121680.0284 | 0.0284 | 1 dígito da parte inteira + parte decimal |
| -121680.0284 | 80.0284 | 2 dígitos da parte inteira + parte decimal |
| -121680.0284 | 680.0284 | 3 dígitos da parte inteira + parte decimal |
| -121680.0284 | 1680.0284 | 4 dígitos da parte inteira + parte decimal |
4. Usarei a função PARSENAME() para obter a parte inteira do Nr_Randomico_Decimal.
PARSENAME(CAST(CHECKSUM(ID_Hex) / 10000.0000 AS decimal(10,4)) , 2) AS Parte_Inteiro
5. Usarei a função PARSENAME() para obter a parte decimal do Nr_Randomico_Decimal.
PARSENAME(CAST(CHECKSUM(ID_Hex) / 10000.0000 AS decimal(10,4)) , 1) AS Parte_Decimal
Note que a função PARSENAME() é composta por dois parâmetros o Objeto e Parte do Objeto que se deseja obter, sintaxe PARSENAME ( ‘object_name’ , object_piece ).
Como nosso objeto é o Número Decimal -121680.0284, ele é composto por 2 partes interpretadas da direita para esquerda. Portanto:
| Objeto | Parte | Resultado | Descrição |
| -121680.0284 | 1 | 0284 | Parte Decimal do Nr_Randomico_Decimal |
| -121680.0284 | 2 | -121680 | Parte Inteira do Nr_Randomico_Decimal |
6. Já com a Parte Inteira, -121680, preciso extrair o 1 dígito, 2 digito, etc.
Para isso usarei a função RIGHT(), obtendo N caracteres da direita para esquerda.
| Parte Inteira | Resultado | Sintaxe |
| -121680 | 0 | RIGHT(‘-121680’, 1) |
| -121680 | 80 | RIGHT(‘-121680’, 2) |
| -121680 | 680 | RIGHT(‘-121680’, 3) |
| -121680 | 1680 | RIGHT(‘-121680’, 4) |
RIGHT(PARSENAME(CAST(CHECKSUM(ID_Hex) / 10000.0000 AS decimal(10,4)), 2), 1) AS Parte_Inteiro_1dig
7. Agora vamos somar a Parte Inteira N Digitos + Parte Decimal
| Nr_Randomico_Decimal | Inteira N Digitos | Parte Decimal | Novos Valores |
| -121680.0284 | 0 | 0284 | 0.0284 |
| -121680.0284 | 80 | 0284 | 80.0284 |
| -121680.0284 | 680 | 0284 | 680.0284 |
| -121680.0284 | 1680 | 0284 | 1680.0284 |
8. Como repetir o comando SQL, várias vezes?
Até o passo sete, foi construído o comando SELECT… que retorna um número aleatório/rondômico.
Porém, precisamos executar o comando SELECT várias vezes e ir armazenando em uma tabela.
Para executar um comando N vezes, usarei o GO que é uma instrução sqlcmd, exemplo:
SELECT ‘A’, 1
GO 5
Será executado 5 vezes o comando SELECT ‘A’, 1
Dito isto, para gerar 5 números randômicos vamos colocar no final do comando SELECT, GO 5
SELECT
A.ID_Hex,
……
GO 5
9. Criar uma Tabela para armazenar os 5 números gerados no passo oito.
No passo anterior, vimos que o comando SELECT foi executado 5 vezes. Isso gerou 5 resultados em resultset diferentes e não uma tabela contendo 5 registros.
Para resolver isso, podemos criar uma Tabela para receber os valores gerados.
Normalmente, usamos a função CREATE TABLE().
Mas, neste exemplo não quero definir cada campo ao criar a tabela de resultado e sim aproveitar o resultado do SELEC para criar a estruturar da #Tabela_NrRandomico que receberá os números randômicos gerados.
Para isso usarei a função TOP() em conjunto com a cláusula INTO do SELECT.
SELECT TOP 0, *
INTO #Tabela_NrRandomico
FROM (
SELECT
A.ID_Hex,
……
) Gerar_NrRandomico
No comando acima o TOP 0 (zero), * do PRIMEIRO SELECT não retorna nenhum valor, porém captura toda estrutura de campos do resultado gerado pelo SEGUNDO SELECT A.ID_HEX.
Já o INTO #Tabela_NrRandomico do PRIMEIRO SELECT, cria a tabela #Tabela_NrRandomico baseado no resultado capturado.
Atenção o # não é erro de digitação, ele significa que será criar uma tabela temporária que ao fechar a conexão com o banco de dados é eliminada, automaticamente.
Para criar uma tabela real, portanto permanente. Retire o #, INTO Tabela_NrRandomico.
10. Inserir na tabela #Tabela_NrRandomico os 5 números gerados no passo oito.
Para isso usarei o INSERT INTO com valores gerados no SELECT
INSERT INTO #Tabela_NrRandomico
SELECT *
FROM (SELECT
A.ID_Hex,
……
) Gerar_NrRandomico
GO 5
11. Vamos exibir o resultado da #Tabela_NrRandomico
SELECT * FROM #Tabela_NrRandomico
GO
12. Excluir a tabela #Tabela_NrRandomico, usando o DROP TABLE.
Para que ao executar o script várias vezes não exiba o erro de tabela já existente, pois ela é sempre criada no passo nove.
DROP TABLE #Tabela_NrRandomico
GO
Com os 12 passos, obtemos o seguinte resultado:
| ID_Hex | Nr_Randomico | Nr_Randomico_Decimal |
| 4CBFDEC3-F57C-433D-B145-7959B570FBC0 | -1216800284 | -121680.0284 |
| 0509C7E0-AEC0-4B6C-8DDF-A26142156E59 | -1025729740 | -102572.9740 |
| 539E79E8-12CC-45AE-9E38-2CBECE009464 | 718090630 | 71809.0630 |
| 38268135-CDCC-43B0-B8D2-68C67C3D0D14 | -2028602481 | -202860.2481 |
| C0743028-43C9-43BE-93AC-5C39455BEA5C | 779214164 | 77921.4164 |
… mais outras colunas com os novos valores Inteiro_1_Decimal, Inteiro_N_Decimal.
Fonte:
https://stackoverflow.com/questions/3418606/sql-how-do-i-get-only-the-numbers-after-the-decimal
https://stackoverflow.com/questions/16412231/how-to-get-2-digits-after-decimal-point-in-tsql
SQL Server – Resolver Erro de Divisão por Zero
Use o código de exemplo, abaixo.
PRINT ‘=============================’
PRINT ‘Variáveis’
PRINT ‘=============================’
declare @dividend float =2
declare @divisor float = 0
PRINT ‘=============================’
PRINT ‘Problema – Erro’
PRINT ‘=============================’
SELECT Dividendo = @dividend
, Divisor = @divisor
, Resultado = @dividend/@divisor
PRINT ‘=============================’
PRINT ‘Solução 1’
PRINT ‘=============================’
SELECT COALESCE(@dividend / NULLIF(@divisor,0), 0)
SELECT Dividendo = @dividend
, Divisor = @divisor
, Resultado = COALESCE(@dividend / NULLIF(@divisor,0), 0)
SQL – Tipos de Objeto
Como saber quais são todas tabelas do banco de dados?
Como saber quais são todas trigger do banco de dados?
Como saber quais são todas constraint do banco de dados?
Para responder perguntas como essa, encontrei a seguinte resposta e resolvi divulgar.
No banco de dados, execute o comando:
SELECT DISTINCT [Type], Type_Desc FROM Sys.Objects ORDER BY [Type]
O resultado será:
A lista completa de tipos, está no site da Microsoft.
Conectividade 