sqlserver的用sql语句导入导出各种格式文件

/*******??导出到excel?
EXEC?master..xp_cmdshell?’bcp?SettleDB.dbo.shanghu?out?c:/temp1.xls?-c?-q?-S"GNETDATA/GNETDATA"?-U"sa"?-P""’?

/***********??导入Excel?
SELECT?*?
FROM?OpenDataSource(?’Microsoft.Jet.OLEDB.4.0’,?
??’Data?Source="c:/test.xls";User?ID=Admin;Password=;Extended?properties=Excel?5.0’)...xactions?


SELECT?cast(cast(科目编号?as?numeric(10,2))?as?nvarchar(255))+’　’?转换后的别名?
FROM?OpenDataSource(?’Microsoft.Jet.OLEDB.4.0’,?
??’Data?Source="c:/test.xls";User?ID=Admin;Password=;Extended?properties=Excel?5.0’)...xactions?

/**?导入文本文件?
EXEC?master..xp_cmdshell?’bcp?"dbname..tablename"?in?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?

/**?导出文本文件?
EXEC?master..xp_cmdshell?’bcp?"dbname..tablename"?out?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?
或?
EXEC?master..xp_cmdshell?’bcp?"Select?*?from?dbname..tablename"?queryout?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?

导出到TXT文本，用逗号分开?
exec?master..xp_cmdshell?’bcp?"库名..表名"?out?"d:/tt.txt"?-c?-t?,-U?sa?-P?password’?


BULK?INSERT?库名..表名?
FROM?’c:/test.txt’?
WITH?(?
????FIELDTERMINATOR?=?’;’,?
????ROWTERMINATOR?=?’/n’?
)?


--/*?dBase?IV文件?
select?*?from?
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’?
,’dBase?IV;HDR=NO;IMEX=2;DATABASE=C:/’,’select?*?from?[客户资料4.dbf]’)?
--*/?

--/*?dBase?III文件?
select?*?from?
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’?
,’dBase?III;HDR=NO;IMEX=2;DATABASE=C:/’,’select?*?from?[客户资料3.dbf]’)?
--*/?

--/*?FoxPro?数据库?
select?*?from?openrowset(’MSDASQL’,?
’Driver=Microsoft?Visual?FoxPro?Driver;SourceType=DBF;SourceDB=c:/’,?
’select?*?from?[aa.DBF]’)?
--*/?

/**************导入DBF文件****************/?
select?*?from?openrowset(’MSDASQL’,?
’Driver=Microsoft?Visual?FoxPro?Driver;?
SourceDB=e:/VFP98/data;?
SourceType=DBF’,?
’select?*?from?customer?where?country?!=?"USA"?order?by?country’)?
go?
/*****************?导出到DBF?***************/?
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句?

insert?into?openrowset(’MSDASQL’,?
’select?*?from?[aa.DBF]’)?
select?*?from?表?

说明:?
SourceDB=c:/??指定foxpro表所在的文件夹?
aa.DBF????????指定foxpro表的文件名.?

?


/*************导出到Access********************/?
insert?into?openrowset(’Microsoft.Jet.OLEDB.4.0’,?
???’x:/A.mdb’;’admin’;’’,A表)?select?*?from?数据库名..B表?

/*************导入Access********************/?
insert?into?B表?selet?*?from?openrowset(’Microsoft.Jet.OLEDB.4.0’,????’x:/A.mdb’;’admin’;’’,A表)?*********************??导入?xml　文件?DECLARE?@idoc?int?DECLARE?@doc?varchar(1000)?--sample?XML?document?SET?@doc?=’?<root>???<Customer?cid=?"C1"?name="Janine"?city="Issaquah">???????<Order?oid="O1"?date="1/20/1996"?amount="3.5"?/>???????<Order?oid="O2"?date="4/30/1997"?amount="13.4">Customer?was?very?satisfied???????</Order>????</Customer>????<Customer?cid="C2"?name="Ursula"?city="Oelde"?>???????<Order?oid="O3"?date="7/14/1999"?amount="100"?note="Wrap?it?blue??????????????white?red">?????????????<Urgency>Important</Urgency>?????????????Happy?Customer.???????</Order>???????<Order?oid="O4"?date="1/20/1996"?amount="10000"/>????</Customer>?</root>?’?--?Create?an?internal?representation?of?the?XML?document.?EXEC?sp_xml_preparedocument?@idoc?OUTPUT,?@doc?--?Execute?a?SELECT?statement?using?OPENXML?rowset?provider.?SELECT?*?FROM?OPENXML?(@idoc,?’/root/Customer/Order’,?1)???????WITH?(oid?????char(5),?????????????amount??float,?????????????comment?ntext?’text()’)?EXEC?sp_xml_removedocument?@idoc?/********************导整个数据库*********************************************/?用bcp实现的存储过程?/*??实现数据导入/导出的存储过程??????????根据不同的参数,可以实现导入/导出整个数据库/单个表??调用示例:?--导出调用示例?----导出单个表?exec?file2table?’zj’,’’,’xzkh_sa..地区资料’,’c:/zj.txt’,1?----导出整个数据库?exec?file2table?’zj’,’xzkh_sa’,’C:/docman’,1?--导入调用示例?----导入单个表?exec?file2table?’zj’,0?----导入整个数据库?exec?file2table?’zj’,0?*/?if?exists(select?1?from?sysobjects?where?name=’File2Table’?and?objectproperty(id,’IsProcedure’)=1)??drop?procedure?File2Table?go?create?procedure?File2Table?@servername?varchar(200)??--服务器名?,@username?varchar(200)???--用户名,如果用NT验证方式,则为空’’?,@password?varchar(200)???--密码?,@tbname?varchar(500)???--数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表?,@filename?varchar(1000)??--导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt?,@isout?bit??????--1为导出,0为导入?as?declare?@sql?varchar(8000)?if?@tbname?like?’%.%.%’?--如果指定了表名,则直接导出单个表?begin??set?@sql=’bcp?’+@tbname???+case?when?@isout=1?then?’?out?’?else?’?in?’?end???+’?"’+@filename+’"?/w’???+’?/S?’+@servername???+case?when?isnull(@username,’’)=’’?then?’’?else?’?/U?’+@username?end???+’?/P?’+isnull(@password,’’)??exec?master..xp_cmdshell?@sql?end?else?begin?--导出整个数据库,定义游标,取出所有的用户表??declare?@m_tbname?varchar(250)??if?right(@filename,1)<>’/’?set?@filename=@filename+’/’??set?@m_tbname=’declare?#tb?cursor?for?select?name?from?’+@tbname+’..sysobjects?where?xtype=’’U’’’??exec(@m_tbname)??open?#tb??fetch?next?from?#tb?into?@m_tbname??while?@@fetch_status=0??begin???set?@sql=’bcp?’+@tbname+’..’+@m_tbname????+case?when?@isout=1?then?’?out?’?else?’?in?’?end????+’?"’+@filename+@m_tbname+’.txt?"?/w’????+’?/S?’+@servername????+case?when?isnull(@username,’’)=’’?then?’’?else?’?/U?’+@username?end????+’?/P?’+isnull(@password,’’)???exec?master..xp_cmdshell?@sql???fetch?next?from?#tb?into?@m_tbname??end??close?#tb??deallocate?#tb??end?go?/**********************Excel导到Txt****************************************/?想用?select?*?into?opendatasource(...)?from?opendatasource(...)?实现将一个Excel文件内容导入到一个文本文件?假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)?且银行帐号导出到文本文件后分两部分，前8位和后8位分开。?如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2?然后就可以用下面的语句进行插入?注意文件名和目录根据你的实际情况进行修改.?insert?into?opendatasource(’MICROSOFT.JET.OLEDB.4.0’?,’Text;HDR=Yes;DATABASE=C:/’?)...[aa#txt]?--,aa#txt)?--*/?select?姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)?from?opendatasource(’MICROSOFT.JET.OLEDB.4.0’?,’Excel?5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’?--,Sheet1$)?)...[Sheet1$]?如果你想直接插入并生成文本文件,就要用bcp?declare?@sql?varchar(8000),@tbname?varchar(50)?--首先将excel表内容导入到一个全局临时表?select?@tbname=’[##temp’+cast(newid()?as?varchar(40))+’]’??,@sql=’select?姓名,8)?into?’+@tbname+’?from?opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’?,’’Excel?5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’’?)...[Sheet1$]’?exec(@sql)?--然后用bcp从全局临时表导出到文本文件?set?@sql=’bcp?"’+@tbname+’"?out?"c:/aa.txt"?/S"(local)"?/P""?/c’?exec?master..xp_cmdshell?@sql?--删除临时表?exec(’drop?table?’+@tbname)?用bcp将文件导入导出到数据库的存储过程:?/*--bcp-二进制文件的导入导出??支持image,text,ntext字段的导入/导出??image适合于二进制文件;text,ntext适合于文本数据文件??注意:导入时,将覆盖满足条件的所有行???导出时,将把所有满足条件的行也出到指定文件中??此存储过程仅用bcp实现?邹建?2003.08-----------------*/?/*--调用示例?--数据导出??exec?p_binaryIO?’zj’,’acc_演示数据..tb’,’img’,’c:/zj1.dat’?--数据导出??exec?p_binaryIO?’zj’,’c:/zj1.dat’,0?--*/?if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N’[dbo].[p_binaryIO]’)?and?OBJECTPROPERTY(id,?N’IsProcedure’)?=?1)?drop?procedure?[dbo].[p_binaryIO]?GO?Create?proc?p_binaryIO?@servename?varchar?(30),--服务器名称?@username?varchar?(30),?--用户名?@password?varchar?(30),?--密码?@tbname?varchar?(500),??--数据库..表名?@fdname?varchar?(30),??--字段名?@fname?varchar?(1000),?--目录+文件名,处理过程中要使用/覆盖:@filename+.bak?@tj?varchar?(1000)=’’,??--处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀?@isout?bit=1???--1导出((默认),0导入?AS?declare?@fname_in?varchar(1000)?--bcp处理应答文件名??,@fsize?varchar(20)???--要处理的文件的大小??,@m_tbname?varchar(50)??--临时表名??,@sql?varchar(8000)?--则取得导入文件的大小?if?@isout=1??set?@fsize=’0’?else?begin??create?table?#tb(可选名?varchar(20),大小?int???,创建日期?varchar(10),创建时间?varchar(20)???,上次写操作日期?varchar(10),上次写操作时间?varchar(20)???,上次访问日期?varchar(10),上次访问时间?varchar(20),特性?int)??insert?into?#tb??exec?master..xp_getfiledetails?@fname??select?@fsize=大小?from?#tb??drop?table?#tb??if?@fsize?is?null??begin???print?’文件未找到’???return??end?end?--生成数据处理应答文件?set?@m_tbname=’[##temp’+cast(newid()?as?varchar(40))+’]’?set?@sql=’select?*?into?’+@m_tbname+’?from(??select?null?as?类型??union?all?select?0?as?前缀??union?all?select?’+@fsize+’?as?长度??union?all?select?null?as?结束??union?all?select?null?as?格式??)?a’?exec(@sql)?select?@fname_in=@fname+’_temp’??,@sql=’bcp?"’+@m_tbname+’"?out?"’+@fname_in??+’"?/S"’+@servename??+case?when?isnull(@username,’’)=’’?then?’’???else?’"?/U"’+@username?end??+’"?/P"’+isnull(@password,’’)+’"?/c’?exec?master..xp_cmdshell?@sql?--删除临时表?set?@sql=’drop?table?’+@m_tbname?exec(@sql)?if?@isout=1?begin??set?@sql=’bcp?"select?top?1?’+@fdname+’?from?’???+@tbname+case?isnull(@tj,’’)?when?’’?then?’’????else?’?where?’+@tj?end???+’"?queryout?"’+@fname???+’"?/S"’+@servename???+case?when?isnull(@username,’’)=’’?then?’’????else?’"?/U"’+@username?end???+’"?/P"’+isnull(@password,’’)???+’"?/i"’+@fname_in+’"’??exec?master..xp_cmdshell?@sql?end?else?begin??--为数据导入准备临时表??set?@sql=’select?top?0?’+@fdname+’?into?’???+@m_tbname+’?from?’?+@tbname??exec(@sql)??--将数据导入到临时表??set?@sql=’bcp?"’+@m_tbname+’"?in?"’+@fname???+’"?/S"’+@servename???+case?when?isnull(@username,’’)???+’"?/i"’+@fname_in+’"’??exec?master..xp_cmdshell?@sql????--将数据导入到正式表中??set?@sql=’update?’+@tbname???+’?set?’+@fdname+’=b.’+@fdname???+’?from?’+@tbname+’?a,’???+@m_tbname+’?b’???+case?isnull(@tj,’’)?when?’’?then?’’????else?’?where?’+@tj?end??exec(@sql)??--删除数据处理临时表??set?@sql=’drop?table?’+@m_tbname?end?--删除数据处理应答文件?set?@sql=’del?’+@fname_in?exec?master..xp_cmdshell?@sql?go?/**?导入文本文件?EXEC?master..xp_cmdshell?’bcp?"dbname..tablename"?in?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?改为如下，不需引号?EXEC?master..xp_cmdshell?’bcp?dbname..tablename?in?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?/**?导出文本文件?EXEC?master..xp_cmdshell?’bcp?"dbname..tablename"?out?c:/DT.txt?-c?-Sservername?-Usa?-Ppassword’?此句需加引号???