SQL Server - Importando e Exportando dados de planilhas do Excel

Como Importar uma planilha do Excel para o SQL Server

A forma mais utilizada com certeza é a leitura de dados de uma planilha do Excel para o banco de dados SQL Server. Para isso, vamos utilizar o OPENROWSET e o driver ACE OLEDB 12.0, uma vez que meu sistema operacional e o meu banco estão na versão 64 bits e utilizando uma planilha do Office 2016 (O plugin JET suporta até a versão 2003).

O comando básico para a leitura é assim:

SELECT * 
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)

Exemplo:

Uma outra forma, que deixa essa integração bem mais fácil, é criar uma Stored Procedure para facilitar a utilização dos dados:

CREATE PROCEDURE [dbo].[stpImporta_Excel](
    @Caminho VARCHAR(5000), 
    @Aba VARCHAR(200), 
    @Colunas VARCHAR(5000)
)
AS
BEGIN

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'SELECT * from OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') A'

    EXEC(@Exec)
 
END

E aí você pode utilizar assim:

Você pode retornar apenas colunas específicas também:

Ou importar o resultado para uma tabela do banco de dados:

Como inserir/exportar dados do SQL Server para o Excel

De uma forma muito simples, também é possível inserir dados do nosso banco de dados para uma planilha do Excel. A sintaxe básica é a seguinte:

INSERT INTO 
OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)
SELECT 'Paulo', 32, 2584.44

Exemplo da inserção:

Exemplo da consulta comprovando que o registro foi inserido:

Inserindo a partir de uma tabela:

Mais uma vez, podemos utilizar uma Stored Procedure para facilitar as operações durante o dia a dia:

CREATE PROCEDURE [dbo].[stpInsere_em_Excel](
    @Caminho VARCHAR(MAX), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas varchar(MAX)
)
AS
BEGIN

    IF (@Colunas = '*')
    BEGIN
    
        SELECT 
            @Colunas = isnull(nullif(@Colunas,'*') + ',','') + b.name
        FROM 
            sysobjects a WITH(NOLOCK)
            JOIN syscolumns b WITH(NOLOCK) ON a.id = b.id
        WHERE 
            a.xtype = 'U'
            AND a.name = @Tabela

    END		
    

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') '
        + 'SELECT '
        + @Colunas
        + ' FROM '
        + @Tabela

    EXEC(@Exec)
 
END

Exemplo de utilização da procedure:

Como atualizar dados de uma planilha do Excel pelo SQL Server

Assim como as outras operações, é possível também atualizar dados de uma planilha do Excel através do SQL Server. A sintaxe básica é essa:

UPDATE A
SET A.Nome = 'Teste 1'
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
) A
WHERE A.Nome = 'Paulo'

Exemplo:

Exemplo com JOIN utilizando tabelas locais do banco de dados:

Stored Procedure para facilitar o dia a dia:

CREATE PROCEDURE [dbo].[stpAtualiza_em_Excel](
    @Caminho varchar(max), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas_Join varchar(max), 
    @Colunas_Update varchar(max)
)
AS
BEGIN
        
    DECLARE 
        @join VARCHAR(MAX) ,
        @update VARCHAR(MAX);


    SELECT  
        @join = ISNULL(@join + ' and ', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Join, ',') AS a;
    

    SELECT  
        @update = ISNULL(@update + ',', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Update, ',') AS a;
    
    
    DECLARE @Exec VARCHAR(MAX)
    
    SET @Exec = 'UPDATE A '
        + 'SET '
        + @update
        + ' FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''Select * From ['
        + @Aba
        + '$]'') A'
        + ' JOIN '
        + @Tabela
        + ' b'
        + ' ON '
        + @join

    EXEC(@Exec)
 
END

Função fncQuebra_Texto necessária para utilizar a SP:

CREATE FUNCTION [dbo].[fncQuebra_Texto] (
    @str NVARCHAR(4000) ,
    @separator CHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH tokens ( p, a, b )
    AS ( 
        SELECT   1, 1, CHARINDEX(@separator, @str)
        UNION ALL
        SELECT   p + 1, b + 1, CHARINDEX(@separator, @str, b + 1)
        FROM     tokens
        WHERE    b > 0
    )
    SELECT  
        p - 1 zeroBasedOccurance ,
        SUBSTRING(@str, a, CASE WHEN b > 0 THEN b - a ELSE 4000 END) AS s
    FROM    
        tokens
);

Utilização da SP: