Olá! Seja bem-vindo!

Integrar dados de um sistema para outro é uma tarefa corriqueira para DBAs e desenvolvedores.

Mais dia menos dia, chegará um momento onde você precisará importar arquivos do Windows para o banco de dados SQL, ou exportar arquivos do banco de dados SQL para o Windows.

No vídeo de hoje veremos como importar e exportar arquivos em massa para tabelas do SQL com apenas 2 scripts genéricos e bastante simples de utilizar no seu ambiente.

Assista aqui ao vídeo de demonstração:

Assista o vídeo de demonstração aqui:

IMPORTANDO ARQUIVOS PARA TABELAS DO SQL

Aqui no blog, já havíamos visto Como Salvar Imagens no SQL Server, porém, na ocasião, utilizamos o comando OPENROWSET em um script estático / hard coded.

No vídeo de hoje, vimos como utilizar o mesmo comando OPENROWSET de forma dinâmica, de maneira que você precisa apenas alterar a pasta onde estão seus arquivos e ele (o script) importará a todos de forma automatizada.

Um ponto de atenção: Antes de executar a importação, é fundamental verificar os pré-requisitos:

1) Confirmar que a máquina do SQL tem acesso de rede a máquina onde estão os arquivos a serem importados;

2) Confirmar que o usuário do serviço do SQL tem acesso a pasta onde estão os arquivos a serem importados;

Você pode testar isso facilmente usando a procedure sys.xp_dirtree, que lista arquivos e pastas.

EXPORTANDO ARQUIVOS DO SQL PARA O WINDOWS

Como vimos no vídeo, exportar arquivos de tabelas do SQL para o seu Windows depende de um pré-requisito importante: Ativar a opção “ole automation procedures” usando a SP_CONFIGURE.

Feito isso, ai é barbada! Só definir a variável @pasta_destino com o caminho onde o script deverá exportar os arquivos usando o objeto “adodb.stream”, que é criado usando a procedure “sys.sp_oacreate”.

Script completo da aula

Baixe o script completo da aula de hoje aqui:

------------------------------------------------------------------
-- Importar e exportar arquivos <-> tabelas no SQL Server
------------------------------------------------------------------

--------------------------------------------------
-- Banco de testes
--------------------------------------------------
set nocount on
go
use master
go
drop database if exists curso
go
create database curso
go
create table curso.dbo.arquivos (
	nm varchar(100) primary key,
	doc varbinary(max) not null
)
go

--------------------------------------------------
-- Requisitos
--------------------------------------------------
/*
1) "Máquina do SQL" precisa ter acesso de rede na máquina onde está a "pasta de arquivos". 
Nesse exemplo, a pasta está no servidor SQL, então não temos dificuldades. 
Se a pasta estiver na rede, é preciso garantir que a "máquina do SQL" tenha conectividade com a "máquina onde estão os arquivos". 

2) "Usuário do serviço do SQL" precisa ter acesso na "pasta dos arquivos".
Nesse exemplo, o usuário do SQL é o padrão da instalação, que é administrador da máquina local onde estão os arquivos, então não temos dificuldades. 
Se a pasta estiver na rede, é preciso garantir que o usuário que roda o serviço do SQL tenha acesso a pasta/compartilhamento onde estão os arquivos. 
Para conferir o usuário que roda o serviço do SQL, acesse a console de serviços em: 
	Iniciar > Executar > digite "services.msc" e clique OK
	Procure pelo serviço do SQL e verifique a credencial de logon

-- Script simples para testar se o SQL tem acesso a pasta: 
-- Se o retorno for "vazio" ou ocorrerem erros como "acesso negado", você terá de ajustar o acesso antes de prosseguir
-- Exemplos:
execute sys.xp_dirtree '\\192.168.59.135\RIS', 1, 1 -- diretório existe, mas o usuário do MEU SQL NÃO tem acesso!
execute sys.xp_dirtree '\\192.168.59.135\Users', 1, 1 -- diretório existe na minha rede e meu SQL tem acesso!
execute sys.xp_dirtree 'c:\tmp\arquivos-originais\', 1, 1 -- diretório local existe e meu SQL tem acesso!
go

*/

--------------------------------------------------
-- Importar: Diretório => SQL
--------------------------------------------------
-- Define a pasta onde estão os arquivos (Conta do serviço do SQL precisa ter acesso de leitura!)
declare @pasta varchar(255) = 'c:\tmp\arquivos-originais\'

-- Lista arquivos da pasta
declare @arquivos table (item varchar(255), depth int, eharquivo bit)
insert into @arquivos execute sys.xp_dirtree @pasta, 1, 1 -- sys.xp_dirtree [diretorio], [nivel de subpasta], [listar arquivos]

-- Salva arquivos no SQL
declare @nm varchar(255)
declare @sql varchar(max)
declare @c cursor 
set @c = cursor for select item from @arquivos where eharquivo = 1
open @c
fetch next from @c into @nm
while @@fetch_status = 0
	begin
		-- Monta comando de inserção do arquivo na base, usando o método openrowset
		set @sql = 'insert into curso.dbo.arquivos (nm, doc) select ''' + @nm + ''', bulkcolumn from openrowset (bulk ''' + @pasta + @nm + ''', single_blob) doc'
		-- Mostra o comando que será executado nas mensagens de retorno e o executa
		print @sql
		execute (@sql)
		fetch next from @c into @nm
	end
go

-- Verificar arquivos importados:
select * from curso.dbo.arquivos
go


--------------------------------------------------
-- Exportar: SQL => Diretório
--------------------------------------------------
-- Requisitos:
execute sys.sp_configure 'show advanced options', 1
go
reconfigure
go
execute sys.sp_configure 'ole automation procedures', 1
go
reconfigure
go


-- Script de exportação:
-- Define pasta de destino para onde os arquivos serão exportados:
declare @pasta_destino varchar(255) = 'c:\tmp\arquivos-exportados-do-sql\'

-- Cria pasta caso não exista:
declare @existe int;
execute master.dbo.xp_fileexist @pasta_destino, @existe out; -- verifica se dir existe
if @existe = 0 execute master.sys.xp_create_subdir @pasta_destino; -- cria, caso não exista

-- Configura componente ADODB.STREAM, que será usado para escrever os dados binários da tabela em arquivos do windows
declare @id_objeto int; 
declare @ds_caminho varchar(100)
execute sys.sp_oacreate 'adodb.stream', @id_objeto output
execute sys.sp_oasetproperty @id_objeto , 'type', 1

-- Define um cursor para leitura da tabela registro a registro:
declare @c cursor; declare @nm varchar(100); declare @doc varbinary(max)
set @c = cursor for select nm, doc from curso.dbo.arquivos
open @c
fetch next from @c into @nm, @doc

-- Salva um arquivo no windows para cada linha da tabela
while @@fetch_status = 0
	begin
		set @ds_caminho = @pasta_destino + @nm
		execute sys.sp_oamethod @id_objeto, 'open'
		execute sys.sp_oamethod @id_objeto, 'write', null, @doc
		execute sys.sp_oamethod @id_objeto,'savetofile', null, @ds_caminho, 2
		execute sys.sp_oamethod @id_objeto,'close'
		fetch next from @c into @nm, @doc
	end

-- Finaliza o script desalocando o componente
execute sys.sp_oadestroy @id_objeto
go



Conclusão
Existem diversas formas e ferramentas para importar e exportar arquivos em massa para tabelas do SQL, inclusive o próprio SSIS “SQL Server Integration Services” pode fazer isso de forma robusta.
Ainda assim, nada mais simples e rápido do que dispormos de uma solução via script, principalmente se esse for genérico e servir para qualquer caso.
Particularmente gosto desse tipo de abordagem, pois permite agilidade especialmente em casos onde estamos resolvendo um incidente urgente.
Abraço do seu amigo Josué