/******* Exportar para Excel
exec master..xp_cmdshell 'bcp Liquidadob.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Importar Excel
selecione *
de opendatasource('microsoft.jet.oledb.4.0',
'data source="c:test.xls";id do usuário=admin;senha=;propriedades estendidas=excel 5.0')...xações
selecione cast(cast(número da conta como numérico(10,2)) como nvarchar(255))+' ' alias convertido
de opendatasource('microsoft.jet.oledb.4.0',
'data source="c:test.xls";id do usuário=admin;senha=;propriedades estendidas=excel 5.0')...xações
/** Importa arquivo de texto
exec master..xp_cmdshell 'bcp dbname..tablename em c:dt.txt -c -sservername -usa -ppassword'
/** Exporta arquivo de texto
exec master..xp_cmdshell 'bcp "dbname..tablename" out c:dt.txt -c -sservername -usa -ppassword'
Esta frase precisa ser colocada entre aspas
ou
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" consulta c:dt.txt -c -sservername -usa -ppassword'
Exportar para texto txt, separado por vírgulas
exec master..xp_cmdshell 'bcp "Nome da biblioteca..Nome da tabela" out "d:tt.txt" -c -t ,-u sa -p senha'
nome da biblioteca de inserção em massa..nome da tabela
de 'c:teste.txt'
com (
terminador de campo = ';',
terminador de linha = 'n'
)
--/* arquivo dbase iv
selecione * de
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','selecione * de [Dados do cliente 4.dbf]')
--*/
--/* arquivo dbase iii
selecione * de
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','selecione * de [Dados do cliente 3.dbf]')
--*/
--/* banco de dados foxpro
selecione * em openrowset('msdasql',
'driver=driver microsoft visual foxpro;sourcetype=dbf;sourcedb=c:',
'selecione * de [aa.dbf]')
--*/
/******************Importar arquivo dbf******************/
selecione * em openrowset('msdasql',
'driver=driver microsoft visual foxpro;
sourcedb=e:vfp98data;
tipo de origem=dbf',
'selecione * do cliente onde país! = "EUA" ordene por país')
ir
/************************ Exportar para dbf ***************/
Se você deseja exportar dados para a tabela foxpro de estrutura gerada (ou seja, existente), você pode usar diretamente a seguinte instrução sql
insira em openrowset('msdasql',
'driver=driver microsoft visual foxpro;sourcetype=dbf;sourcedb=c:',
'selecione * de [aa.dbf]')
selecione * da tabela
ilustrar:
sourcedb=c: especifica a pasta onde a tabela foxpro está localizada
aa.dbf especifica o nome do arquivo da tabela foxpro.
/****************Exportar para acessar**********************/
insira em openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',uma tabela) selecione * no nome do banco de dados..b tabela
/******************importar acesso************************/
insira na tabela b selet * de openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',uma tabela)
********************* Importar arquivo xml
declare @idoc int
declare @doc varchar(1000)
--exemplo de documento xml
definir @doc ='
<raiz>
<cliente cid= "c1" name="janine" city="issaquah">
<pedido oid="o1" data="20/01/1996" quantidade="3,5" />
<order oid="o2" date="4/30/1997" amount="13.4">o cliente ficou muito satisfeito
</ordem>
</cliente>
<cliente cid="c2" nome="ursula" cidade="oelde" >
<order oid="o3" date="7/14/1999" amount="100" note="embrulhe azul
branco vermelho">
<urgência>importante</urgência>
cliente feliz.
</ordem>
<pedido oid="o4" data="20/01/1996" quantidade="10000"/>
</cliente>
</raiz>
'
-- crie uma representação interna do documento xml.
exec sp_xml_preparedocument @idoc saída, @doc
- execute uma instrução select usando o provedor de conjunto de linhas openxml.
selecione *
de openxml (@idoc, '/root/customer/order', 1)
com (oid char(5),
valor flutuante,
comente ntext 'texto()')
exec sp_xml_removedocument @idoc
/**********************Importar todo o banco de dados************************ ***** *******************/
Procedimento armazenado implementado usando bcp
/*
Implementar procedimentos armazenados para importação/exportação de dados
De acordo com diferentes parâmetros, você pode importar/exportar todo o exemplo de chamada de banco de dados/tabela única:
--exportar exemplo de chamada
----Exportar uma única tabela
exec file2table 'zj','','','xzkh_sa..informações da região','c:zj.txt',1
----Exportar todo o banco de dados
exec arquivo2table 'zj','','','xzkh_sa','c:docman',1
--Importar exemplo de chamada
----Importar uma única tabela
exec file2table 'zj','','','xzkh_sa..informações da região','c:zj.txt',0
----Importar todo o banco de dados
exec arquivo2table 'zj','','','xzkh_sa','c:docman',0
*/
se existir (selecione 1 em sysobjects onde name='file2table' e objectproperty(id,'isprocedure')=1)
descartar procedimento file2table
ir
criar procedimento file2table
@nomeservidor varchar(200) --nome do servidor
,@username varchar(200) --Nome de usuário, se estiver usando o método de verificação nt, estará vazio''
,@senha varchar(200) --Senha
,@tbname varchar(500) --database.dbo.table name Se você não especificar:.dbo.table name, todas as tabelas de usuário do banco de dados serão exportadas.
,@filename varchar(1000) --Caminho de importação/exportação/nome do arquivo Se o parâmetro @tbname indicar a exportação de todo o banco de dados, então este parâmetro é o caminho de armazenamento do arquivo e o nome do arquivo usa automaticamente o nome da tabela.txt.
,@isout bit --1 é exportação, 0 é importação
como
declare @sql varchar(8000)
if @tbname like '%.%.%' --Se um nome de tabela for especificado, uma única tabela será exportada diretamente
começar
set @sql='bcp '+@nometb
+case quando @isout=1 then 'out' else 'in' end
+' " '+@nome do arquivo+' " /w'
+' /s '+@nomeservidor
+case quando isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@senha,'')
mestre executivo..xp_cmdshell @sql
fim
outro
Begin --exporta todo o banco de dados, define cursores e remove todas as tabelas de usuários
declare @m_tbname varchar(250)
if right(@filename,1)<>'' set @filename=@filename+''
set @m_tbname='declare #tb cursor para selecionar o nome de '+@tbname+'..sysobjects onde xtype=''u'''
exec(@m_tbname)
abra #tb
buscar o próximo de #tb em @m_tbname
enquanto @@fetch_status=0
começar
set @sql='bcp '+@nometb+'..'+@m_nometb
+case quando @isout=1 then 'out' else 'in' end
+' " '+@nomedoarquivo+@m_nometb+'.txt " /w'
+' /s '+@nomeservidor
+case quando isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@senha,'')
mestre executivo..xp_cmdshell @sql
buscar o próximo de #tb em @m_tbname
fim
fechar #tb
desalocar #tb
fim
ir
/**********************excel para txt************************ ****** *******************/
Quer usar
selecione * em opendatasource(...) de opendatasource(...)
Implementação da importação do conteúdo de um arquivo Excel para um arquivo texto
Suponha que haja duas colunas no Excel, a primeira coluna é o nome e a segunda coluna é o número da conta (16 dígitos)
E o número da conta bancária é dividido em duas partes após a exportação para um arquivo de texto, os primeiros 8 dígitos e os últimos 8 dígitos são separados.
Caso queira inseri-lo utilizando o extrato acima, o arquivo texto deve existir e ter uma linha: nome, conta bancária número 1, conta bancária número 2
Então você pode usar a seguinte instrução para inserir. Observe que o nome do arquivo e o diretório podem ser modificados de acordo com sua situação real.
inserir em
fonte de dados aberta('microsoft.jet.oledb.4.0'
,'texto;hdr=sim;banco de dados=c:'
)...[aa#txt]
--,aa#txt)
--*/
selecione o nome, número da conta bancária 1 = esquerda (número da conta bancária, 8), número da conta bancária 2 = direita (número da conta bancária, 8)
de
fonte de dados aberta('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=sim;imex=2;banco de dados=c:a.xls'
--, planilha1$)
)...[folha1$]
Se você deseja inserir e gerar arquivos de texto diretamente, use bcp
declare @sql varchar(8000),@tbname varchar(50)
--Primeiro importe o conteúdo da tabela Excel para uma tabela temporária global
selecione @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='selecione nome, número da conta bancária 1 = esquerda (número da conta bancária, 8), número da conta bancária 2 = direita (número da conta bancária, 8)
em '+@tbname+' de
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=sim;imex=2;banco de dados=c:a.xls''
)...[folha1$]'
exec(@sql)
--Em seguida, use bcp para exportar da tabela temporária global para um arquivo de texto
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(local)" /p"" /c'
mestre executivo..xp_cmdshell @sql
--Excluir tabela temporária
exec('descartar tabela '+@nometb )
Procedimento armazenado para importação e exportação de arquivos para o banco de dados usando bcp:
/*--bcp-Importação e exportação de arquivos binários
Suporta importação/exportação de campos de imagem, texto, ntext
image é adequado para arquivos de texto binários, ntext é adequado para arquivos de dados de texto;
Nota: Ao importar, todas as linhas que atendem às condições serão substituídas
Ao exportar, todas as linhas que atendam às condições também serão exportadas para o arquivo especificado.
Este procedimento armazenado usa apenas bcp para implementar Zou Jian 2003.08-----------------*/
/*--Exemplo de chamada
--Exportação de dados
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--Exportação de dados
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
se existir (selecione * em dbo.sysobjects onde id = object_id(n'[dbo].[p_binaryio]') e objectproperty(id, n'isprocedure') = 1)
procedimento de eliminação [dbo].[p_binaryio]
ir
criar proc p_binaryio
@servename varchar (30),--nome do servidor
@nomedeusuário varchar (30), --nomedeusuário
@senha varchar (30), --senha
@tbname varchar (500), --database..nome da tabela
@fdname varchar (30), --Nome do campo
@fname varchar (1000), --directory + nome do arquivo, usar/substituir durante o processamento: @filename+.bak
@tj varchar (1000)='', --processing condições Para importação de dados, se a condição contiver @fdname, especifique o prefixo do nome da tabela.
@isout bit=1 --1 exportação ((padrão), 0 importação
como
declare @fname_in varchar(1000) --bcp processando o nome do arquivo de resposta
,@fsize varchar(20) --O tamanho do arquivo a ser processado
,@m_tbname varchar(50) --nome da tabela temporária
,@sqlvarchar(8000)
--Obter o tamanho do arquivo importado
se @isout=1
definir @fsize='0'
outro
começar
criar tabela #tb (nome opcional varchar (20), tamanho int
, data de criação varchar(10), hora de criação varchar(20)
, data da última operação de gravação varchar(10), hora da última operação de gravação varchar(20)
,data do último acesso varchar(10), hora do último acesso varchar(20),característica int)
insira em #tb
exec master..xp_getfiledetails @fname
selecione @fsize=sizefrom #tb
soltar tabela #tb
se @fsize for nulo
começar
imprima 'Arquivo não encontrado'
retornar
fim
fim
--Gerar arquivo de resposta de processamento de dados
definir @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
defina @sql='select * em '+@m_tbname+' from(
selecione nulo como tipo
união todos selecionam 0 como prefixo
união todos selecionam '+@fsize+' como comprimento
união todos selecionam nulo como fim
união todos selecionam nulo como formato
) uma'
exec(@sql)
selecione @fname_in=@fname+'_temp'
,@sql='bcp " '+@m_tbname+' " fora " '+@fname_in
+'" /s" '+@servename
+case quando isnull(@username,'')='' então ''
else '" /u" '+@nome de usuário fim
+'" /p"'+isnull(@senha,'')+'" /c'
mestre executivo..xp_cmdshell @sql
--Excluir tabela temporária
set @sql='descartar tabela '+@m_tbname
exec(@sql)
se @isout=1
começar
set @sql='bcp "selecione o primeiro lugar '+@fdname+' de '
+@tbname+case isnull(@tj,'') quando '' then ''
else 'onde '+@tj fim
+'" consulta " '+@fname
+'" /s" '+@servename
+case quando isnull(@username,'')='' then ''
else '" /u" '+@nome de usuário fim
+'" /p"'+isnull(@senha,'')
+'" /i" '+@fname_in+'"'
mestre executivo..xp_cmdshell @sql
fim
outro
começar
--Prepare tabelas temporárias para importação de dados
defina @sql='selecione top 0 '+@fdname+' em '
+@m_nome_tb+' de ' +@nometb
exec(@sql)
--Importa dados para uma tabela temporária
defina @sql='bcp " '+@m_tbname+' " em " '+@fname
+'" /s" '+@servename
+case quando isnull(@username,'')='' then ''
else '" /u" '+@nome de usuário fim
+'" /p"'+isnull(@senha,'')
+'" /i" '+@fname_in+'"'
mestre executivo..xp_cmdshell @sql
--Importar dados para tabelas formais
set @sql='update '+@tbname
+' definir '+@fdname+'=b.'+@fdname
+' de '+@tbname+' a,'
+@m_nome_tb+' b'
+case isnull(@tj,'') quando '' então ''
else 'onde '+@tj fim
exec(@sql)
--Excluir tabela temporária de processamento de dados
set @sql='descartar tabela '+@m_tbname
fim
--Excluir arquivo de resposta de processamento de dados
definir @sql='del '+@fname_in
mestre executivo..xp_cmdshell @sql