导出命令大全,SQL语句导入导出大全

MSSQLServer数据库SQL语句导入导出大全,包括与其他数据库和文件的数据的导入导出。
/*******导出到excel
EXECmaster..xp_cmdshell’bcpSettleDB.dbo.shanghuoutc:temp1.xls-c-q-S“GNETDATA/GNETDATA“-U“sa“-P““’
/***********导入Excel SELECT*
FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,
‘DataSource=“c:test.xls“;UserID=Admin;Password=;Extendedproperties=Excel5.0′)…xactions
SELECTcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+’ ‘转换后的别名
FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,
‘DataSource=“c:test.xls“;UserID=Admin;Password=;Extendedproperties=Excel5.0′)…xactions
/**导入文本文件
EXECmaster..xp_cmdshell’bcp“dbname..tablename“inc:DT.txt-c-Sservername-Usa-Ppassword’
/**导出文本文件
EXECmaster..xp_cmdshell’bcp“dbname..tablename“outc:DT.txt-c-Sservername-Usa-Ppassword’

EXECmaster..xp_cmdshell’bcp“Select*fromdbname..tablename“queryoutc:DT.txt-c-Sservername-Usa-Ppassword’
导出到TXT文本,用逗号分开
execmaster..xp_cmdshell’bcp“库名..表名“out“d:tt.txt“-c-t,-Usa-Ppassword’
BULKINSERT库名..表名 FROM’c:test.txt’ WITH( FIELDTERMINATOR=’;’,
ROWTERMINATOR=’n’ ) –/*dBaseIV文件 select*from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′
,’dBaseIV;HDR=NO;IMEX=2;DATABASE=C:’,’select*from[客户资料4.dbf]’)
–*/ –/*dBaseIII文件 select*from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′
,’dBaseIII;HDR=NO;IMEX=2;DATABASE=C:’,’select*from[客户资料3.dbf]’)
–*/ –/*FoxPro数据库 select*fromopenrowset(‘MSDASQL’,
‘Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:’,
‘select*from[aa.DBF]’) –*/
/**************导入DBF文件****************/
select*fromopenrowset(‘MSDASQL’, ‘Driver=MicrosoftVisualFoxProDriver;
SourceDB=e:VFP98data; SourceType=DBF’,
‘select*fromcustomerwherecountry!=“USA“orderbycountry’) go
/*****************导出到DBF***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insertintoopenrowset(‘MSDASQL’,
‘Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:’,
‘select*from[aa.DBF]’) select*from表 说明:
SourceDB=c:指定foxpro表所在的文件夹 aa.DBF指定foxpro表的文件名.
/*************导出到Access********************/
insertintoopenrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:A.mdb’;’admin’;”,A表)select*from数据库名..B表
/*************导入Access********************/
insertintoB表selet*fromopenrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:A.mdb’;’admin’;”,A表)
*********************导入xml 文件 DECLARE@idocint
DECLARE@docvarchar(1000) –sampleXMLdocument SET@doc=’ 〈root〉
〈Customercid=“C1“name=“Janine“city=“Issaquah“〉
〈Orderoid=“O1“date=“1/20/1996“amount=“3.5“/〉
〈Orderoid=“O2“date=“4/30/1997“amount=“13.4“〉Customerwasverysatisfied
〈/Order〉 〈/Customer〉 〈Customercid=“C2“name=“Ursula“city=“Oelde“〉
〈Orderoid=“O3“date=“7/14/1999“amount=“100“note=“Wrapitblue whitered“〉
〈Urgency〉Important〈/Urgency〉 HappyCustomer. 〈/Order〉
〈Orderoid=“O4“date=“1/20/1996“amount=“10000“/〉 〈/Customer〉 〈/root〉
‘ –CreateaninternalrepresentationoftheXMLdocument.
EXECsp_xml_preparedocument@idocOUTPUT,@doc
–ExecuteaSELECTstatementusingOPENXMLrowsetprovider. SELECT*
FROMOPENXML(@idoc,’/root/Customer/Order’,1) WITH(oidchar(5),
amountfloat, commentntext’text()’) EXECsp_xml_removedocument@idoc
/********************导整个数据库*********************************************/
用bcp实现的存储过程 /* 实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表 调用示例:
–导出调用示例 —-导出单个表
execfile2table’zj’,”,”,’xzkh_sa..地区资料’,’c:zj.txt’,1
—-导出整个数据库 execfile2table’zj’,”,”,’xzkh_sa’,’C:docman’,1
–导入调用示例 —-导入单个表
execfile2table’zj’,”,”,’xzkh_sa..地区资料’,’c:zj.txt’,0
—-导入整个数据库 execfile2table’zj’,”,”,’xzkh_sa’,’C:docman’,0 */
ifexists(select1fromsysobjectswherename=’File2Table’andobjectproperty(id,’IsProcedure’)=1)
dropprocedureFile2Table go createprocedureFile2Table
@servernamevarchar(200)–服务器名
,@usernamevarchar(200)–用户名,如果用NT验证方式,则为空”
,@passwordvarchar(200)–密码
,@tbnamevarchar(500)–数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filenamevarchar(1000)–导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isoutbit–1为导出,0为导入 as declare@sqlvarchar(8000)
if@tbnamelike’%.%.%’–如果指定了表名,则直接导出单个表 begin
set@sql=’bcp’+@tbname +casewhen@isout=1then’out’else’in’end
+’“’+@filename+’“/w’ +’/S’+@servername
+casewhenisnull(@username,”)=”then”else’/U’+@usernameend
+’/P’+isnull(@password,”) execmaster..xp_cmdshell@sql end else
begin–导出整个数据库,定义游标,取出所有的用户表
declare@m_tbnamevarchar(250)
ifright(@filename,1)〈〉”set@filename=@filename+”
set@m_tbname=’declare#tbcursorforselectnamefrom’+@tbname+’..sysobjectswherextype=”U”’
exec(@m_tbname) open#tb fetchnextfrom#tbinto@m_tbname
while@@fetch_status=0 begin set@sql=’bcp’+@tbname+’..’+@m_tbname
+casewhen@isout=1then’out’else’in’end
+’“’+@filename+@m_tbname+’.txt“/w’ +’/S’+@servername
+casewhenisnull(@username,”)=”then”else’/U’+@usernameend
+’/P’+isnull(@password,”) execmaster..xp_cmdshell@sql
fetchnextfrom#tbinto@m_tbname end close#tb deallocate#tb end go
/**********************Excel导到Txt****************************************/
想用 select*intoopendatasource(…)fromopendatasource(…)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分,前8位和后8位分开。
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入 注意文件名和目录根据你的实际情况进行修改.
insertinto 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′
,’Excel5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls’ –,Sheet1$) )…[Sheet1$]
如果你想直接插入并生成文本文件,就要用bcp
declare@sqlvarchar(8000),@tbnamevarchar(50)
–首先将excel表内容导入到一个全局临时表
select@tbname='[##temp’+cast(newid()asvarchar(40))+’]’
,@sql=’select姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into’+@tbname+’from opendatasource(”MICROSOFT.JET.OLEDB.4.0”
,”Excel5.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’
execmaster..xp_cmdshell@sql –删除临时表 exec(‘droptable’+@tbname)
用bcp将文件导入导出到数据库的存储过程: /*–bcp-二进制文件的导入导出
支持image,text,ntext字段的导入/导出
image适合于二进制文件;text,ntext适合于文本数据文件
注意:导入时,将覆盖满足条件的所有行
导出时,将把所有满足条件的行也出到指定文件中 此存储过程仅用bcp实现
邹建2003.08—————–*/ /*–调用示例 –数据导出
execp_binaryIO’zj’,”,”,’acc_演示数据..tb’,’img’,’c:zj1.dat’
–数据导出
execp_binaryIO’zj’,”,”,’acc_演示数据..tb’,’img’,’c:zj1.dat’,”,0
–*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_binaryIO]’)andOBJECTPROPERTY(id,N’IsProcedure’)=1)
dropprocedure[dbo].[p_binaryIO] GO Createprocp_binaryIO
@servenamevarchar(30),–服务器名称 @usernamevarchar(30),–用户名
@passwordvarchar(30),–密码 @tbnamevarchar(500),–数据库..表名
@fdnamevarchar(30),–字段名
@fnamevarchar(1000),–目录+文件名,处理过程中要使用/覆盖:@filename+.bak
@tjvarchar(1000)=”,–处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
@isoutbit=1–1导出((默认),0导入 AS
declare@fname_invarchar(1000)–bcp处理应答文件名
,@fsizevarchar(20)–要处理的文件的大小 ,@m_tbnamevarchar(50)–临时表名
,@sqlvarchar(8000) –则取得导入文件的大小 if@isout=1 set@fsize=’0′ else
begin createtable#tb(可选名varchar(20),大小int
,创建日期varchar(10),创建时间varchar(20)
,上次写操作日期varchar(10),上次写操作时间varchar(20)
,上次访问日期varchar(10),上次访问时间varchar(20),特性int) insertinto#tb
execmaster..xp_getfiledetails@fname select@fsize=大小from#tb
droptable#tb if@fsizeisnull begin print’文件未找到’ return end end
–生成数据处理应答文件
set@m_tbname='[##temp’+cast(newid()asvarchar(40))+’]’
set@sql=’select*into’+@m_tbname+’from( selectnullas类型
unionallselect0as前缀 unionallselect’+@fsize+’as长度
unionallselectnullas结束 unionallselectnullas格式 )a’ exec(@sql)
select@fname_in=@fname+’_temp’
,@sql=’bcp“’+@m_tbname+’“out“’+@fname_in +’“/S“’+@servename
+casewhenisnull(@username,”)=”then” else’“/U“’+@usernameend
+’“/P“’+isnull(@password,”)+’“/c’ execmaster..xp_cmdshell@sql
–删除临时表 set@sql=’droptable’+@m_tbname exec(@sql) if@isout=1 begin
set@sql=’bcp“selecttop1’+@fdname+’from’
+@tbname+caseisnull(@tj,”)when”then” else’where’+@tjend
+’“queryout“’+@fname +’“/S“’+@servename
+casewhenisnull(@username,”)=”then” else’“/U“’+@usernameend
+’“/P“’+isnull(@password,”) +’“/i“’+@fname_in+’“’
execmaster..xp_cmdshell@sql end else begin –为数据导入准备临时表
set@sql=’selecttop0’+@fdname+’into’ +@m_tbname+’from’+@tbname
exec(@sql) –将数据导入到临时表 set@sql=’bcp“’+@m_tbname+’“in“’+@fname
+’“/S“’+@servename +casewhenisnull(@username,”)=”then”
else’“/U“’+@usernameend +’“/P“’+isnull(@password,”)
+’“/i“’+@fname_in+’“’ execmaster..xp_cmdshell@sql
–将数据导入到正式表中 set@sql=’update’+@tbname
+’set’+@fdname+’=b.’+@fdname +’from’+@tbname+’a,’ +@m_tbname+’b’
+caseisnull(@tj,”)when”then” else’where’+@tjend exec(@sql)
–删除数据处理临时表 set@sql=’droptable’+@m_tbname end
–删除数据处理应答文件 set@sql=’del’+@fname_in
execmaster..xp_cmdshell@sql go /**导入文本文件
EXECmaster..xp_cmdshell’bcp“dbname..tablename“inc:DT.txt-c-Sservername-Usa-Ppassword’
改为如下,不需引号
EXECmaster..xp_cmdshell’bcpdbname..tablenameinc:DT.txt-c-Sservername-Usa-Ppassword’
/**导出文本文件
EXECmaster..xp_cmdshell’bcp“dbname..tablename“outc:DT.txt-c-Sservername-Usa-Ppassword’
此句需加引号

 

导出到excel
EXECmaster..xp_cmdshell’bcpSettleDB.dbo.shanghuoutc:temp1.xls-c-q-S”GNETDATA/GNETDATA”-U”sa”-P””‘导入Excel
SELECT*FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0′,’DataSource=”c:test.xls”;UserID=Admin;Password=;Extendedproperties=Excel5.0’)…xactions
动态文件名 declare@fnvarchar(20),@svarchar(1000)
set@fn=’c:test.xls’ set@s=”’Microsoft.Jet.OLEDB.4.0”,
”DataSource=”‘+@fn+'”;UserID=Admin;Password=;Extendedproperties=Excel5.0”’
set@s=’SELECT*FROMOpenDataSource(‘+@s+’)…sheet1$’ exec(@s)
SELECTcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+’ ‘转换后的别名
FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,
‘DataSource=”c:test.xls”;UserID=Admin;Password=;Extendedproperties=Excel5.0’)…xactions
EXCEL导到远程SQL insertOPENDATASOURCE( ‘SQLOLEDB’,
‘DataSource=远程ip;UserID=sa;Password=密码’ ).库名.dbo.表名(列名1,列名2)
SELECT列名1,列名2 FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,
‘DataSource=”c:test.xls”;UserID=Admin;Password=;Extendedproperties=Excel5.0’)…xactions
导入文本文件
EXECmaster..xp_cmdshell’bcpdbname..tablenameinc:DT.txt-c-Sservername-Usa-Ppassword’
导出文本文件
EXECmaster..xp_cmdshell’bcpdbname..tablenameoutc:DT.txt-c-Sservername-Usa-Ppassword’

EXECmaster..xp_cmdshell’bcp”Select*fromdbname..tablename”queryoutc:DT.txt-c-Sservername-Usa-Ppassword’
导出到TXT文本,用逗号分开
execmaster..xp_cmdshell’bcp”库名..表名”out”d:tt.txt”-c-t,-Usa-Ppassword’
BULKINSERT库名..表名 FROM’c:test.txt’ WITH( FIELDTERMINATOR=’;’,
ROWTERMINATOR=’n’ ) –/*dBaseIV文件 select*from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′
,’dBaseIV;HDR=NO;IMEX=2;DATABASE=C:’,’select*from[客户资料4.dbf]’)
–*/ –/*dBaseIII文件 select*from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′
,’dBaseIII;HDR=NO;IMEX=2;DATABASE=C:’,’select*from[客户资料3.dbf]’)
–*/ –/*FoxPro数据库 select*fromopenrowset(‘MSDASQL’,
‘Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:’,
‘select*from[aa.DBF]’) –*/ 导入DBF文件
select*fromopenrowset(‘MSDASQL’, ‘Driver=MicrosoftVisualFoxProDriver;
SourceDB=e:VFP98data; SourceType=DBF’,
‘select*fromcustomerwherecountry!=”USA”orderbycountry’) go
导出到DBF
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insertintoopenrowset(‘MSDASQL’,
‘Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:’,
‘select*from[aa.DBF]’) select*from表 说明:
SourceDB=c:指定foxpro表所在的文件夹 aa.DBF指定foxpro表的文件名.
导出到Access insertintoopenrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:A.mdb’;’admin’;”,A表)select*from数据库名..B表 导入Access
insertintoB表selet*fromopenrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:A.mdb’;’admin’;”,A表) 文件名为参数 declare@fnamevarchar(20)
set@fname=’d:test.mdb’
exec(‘SELECTa.*FROMopendatasource(”Microsoft.Jet.OLEDB.4.0”,
”’+@fname+”’;”admin”;””,topics)asa’) SELECT*
FROMOpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,
‘DataSource=”f:northwind.mdb”;JetOLEDB:DatabasePassword=123;UserID=Admin;Password=;’)
导入xml 文件[Page] DECLARE@idocint DECLARE@docvarchar(1000)
–sampleXMLdocument SET@doc=’ root Customercname=”Janine”city=”Issaquah”
Orderodate=”1/20/1996″amount=”3.5″/
Orderodate=”4/30/1997″amount=”13.4″Customerwasverysatisfied /Order
/Customer Customercname=”Ursula”city=”Oelde”
Orderodate=”7/14/1999″amount=”100″note=”Wrapitblue whitered”
UrgencyImportant/Urgency HappyCustomer. /Order
Orderodate=”1/20/1996″amount=”10000″/ /Customer /root ‘
–CreateaninternalrepresentationoftheXMLdocument.
EXECsp_xml_preparedocument@idocOUTPUT,@doc
–ExecuteaSELECTstatementusingOPENXMLrowsetprovider. SELECT*
FROMOPENXML(@idoc,’/root/Customer/Order’,1) WITH(oidchar(5),
amountfloat, commentntext’text()’) EXECsp_xml_removedocument@idoc
Excel导到Txt 想用
select*intoopendatasource(…)fromopendatasource(…)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分,前8位和后8位分开。
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2,然后就可以用下面的语句进行插入,注意文件名和目录根据你的实际情况进行修改.
insertinto 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′
,’Excel5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls’ –,Sheet1$)
)…[Sheet1$] 如果你想直接插入并生成文本文件,就要用bcp
declare@sqlvarchar(8000),@tbnamevarchar(50)
–首先将excel表内容导入到一个全局临时表
select@tbname='[##temp’+cast(newid()asvarchar(40))+’]’
,@sql=’select姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into’+@tbname+’from opendatasource(”MICROSOFT.JET.OLEDB.4.0”
,”Excel5.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’
execmaster..xp_cmdshell@sql –删除临时表 exec(‘droptable’+@tbname)
导整个数据库 用bcp实现的存储过程 /* 实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表 调用示例:
–导出调用示例 —-导出单个表
execfile2table’zj’,”,”,’xzkh_sa..地区资料’,’c:zj.txt’,1
—-导出整个数据库 execfile2table’zj’,”,”,’xzkh_sa’,’C:docman’,1
–导入调用示例 —-导入单个表
execfile2table’zj’,”,”,’xzkh_sa..地区资料’,’c:zj.txt’,0
—-导入整个数据库 execfile2table’zj’,”,”,’xzkh_sa’,’C:docman’,0
*/
ifexists(select1fromsysobjectswherename=’File2Table’andobjectproperty(id,’IsProcedure’)=1)
dropprocedureFile2Table go createprocedureFile2Table
@servernamevarchar(200)–服务器名
,@usernamevarchar(200)–用户名,如果用NT验证方式,则为空”
,@passwordvarchar(200)–密码
,@tbnamevarchar(500)–数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filenamevarchar(1000)–导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isoutbit–1为导出,0为导入 as declare@sqlvarchar(8000)
if@tbnamelike’%.%.%’–如果指定了表名,则直接导出单个表 begin
set@sql=’bcp’+@tbname +casewhen@isout=1then’out’else’in’end
+'”‘+@filename+'”/w’ +’/S’+@servername
+casewhenisnull(@username,”)=”then”else’/U’+@usernameend
+’/P’+isnull(@password,”) execmaster..xp_cmdshell@sql end else
begin–导出整个数据库,定义游标,取出所有的用户表
declare@m_tbnamevarchar(250)
ifright(@filename,1)”set@filename=@filename+”
set@m_tbname=’declare#tbcursorforselectnamefrom’+@tbname+’..sysobjectswherextype=”U”’
exec(@m_tbname) open#tb fetchnextfrom#tbinto@m_tbname
while@@fetch_status=0 begin set@sql=’bcp’+@tbname+’..’+@m_tbname
+casewhen@isout=1then’out’else’in’end
+'”‘+@filename+@m_tbname+’.txt”/w’ +’/S’+@servername
+casewhenisnull(@username,”)=”then”else’/U’+@usernameend
+’/P’+isnull(@password,”) execmaster..xp_cmdshell@sql
fetchnextfrom#tbinto@m_tbname end close#tb deallocate#tb end go
Oracle EXECsp_addlinkedserver’OracleSvr’, ‘Oracle7.3’, ‘MSDAORA’,
‘ORCLDB’ GO deletefromopenquery(mailser,’select*fromyulin’)
select*fromopenquery(mailser,’select*fromyulin’)
updateopenquery(mailser,’select*fromyulinwhereid=15′)setdisorder=555,catago=888
insertintoopenquery(mailser,’selectdisorder,catagofromyulin’)values(333,777)
补充: 对于用bcp导出,是没有字段名的. 用openrowset导出,需要事先建好表.
用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

/******* 导出到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:VFP98data;
SourceType=DBF’,
’select * from customer where country != “USA” order by country’)
go
/***************** 导出到DBF
***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:’,
’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’,’’,’’,’xzkh_sa..地区资料’,’c:zj.txt’,0
—-导入整个数据库
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:docman’,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
姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,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’,’’,’’,’acc_演示数据..tb’,’img’,’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,’’)=’’ then ’’
else ’” /U”’+@username end
+’” /P”’+isnull(@password,’’)
+’” /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’
此句需加引号

发表评论

电子邮件地址不会被公开。 必填项已用*标注