<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
导入导出大全(2004-12-13)
http://builderman.home.sunbo.net/show_hdr.php?xname=U53DTV0&dname=517DTV0&xpos=1
一、打开另外一个
1.
打开SQLserver2000的另一个数据库的表
(1). select*fromopenrowset('sqloledb','sprogram';'develop';'11111','select*fromkind')
(2). select*fromopenrowset('sqloledb','190.1.1.247';'develop';'11111','select*fromkind')
(3). select*fromopendatasource('sqloledb','datasource=sprogram;userID=develop;Password=11111').new_ks.dbo.kind
2.
打开2000的一个表
(1). select* fromOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource=e:/north.mdb;UserID=Admin;Password=')...supply
(2). select*fromopenrowset('Microsoft.Jet.OLEDB.4.0',
'e:/north.mdb';'admin';'',supply)
(3). select*fromopenrowset('Microsoft.Jet.OLEDB.4.0',
'e:/north.mdb';'admin';'','select*fromsupplywhere供应商编号>10')
3.
打开的一个表
(1).select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=e:/test.xls',sheet1$)
(2).select*into表fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=e:/test1.xls',sheet3$)
二、导入与导出
1. 用BCP工具导入导出
(1)xls文件
EXECmaster..xp_cmdshell'bcp"SELECTclass_no,kind_no,cn_nameFROMnew_ks.dbo.kindORDERBY1,2"queryout"e:/test3.xls"-c-q-S"sprogram"-U"develop"-P"12345"'
(2)/**导入文本文件
--入SQLServer
select* into你的表fromOpenRowset('MSDASQL','Driver={MicrosoftTextDriver(*.txt;*.csv)};DefaultDir=c:/temp;','select*fromcontact.txt')
--查询导出
EXECmaster..xp_cmdshell'bcp"SELECTclass_no,kind_no,cn_nameFROMnew_ks.dbo.kindORDERBY1,2"queryout"e:/TX1.txt"-c-q-S"sprogram"-U"develop"-P"12345"'
--直接导出
EXECmaster..xp_cmdshell'bcpnew_ks.dbo.classout"e:/TX3.txt"-c-S"sprogram"-U"develop"-P"12345"'
--直接导入
EXECmaster..xp_cmdshell'bcp"数据库.dbo.数据表"inc:/DT.txt-c-S服务器名-U用户-P密码'
EXECmaster..xp_cmdshell'bcphdh.dbo.kindin"e:/TX1.txt"-c-q-S-U-P'
EXECmaster..xp_cmdshell'bcphdh.dbo.kindin"e:/test3.xls"-c-q-S-U-P'
--用BULKINSERT导入
BULKINSERThdh.dbo.kind
FROM'e:/tx1.txt'
WITH(
FIELDTERMINATOR='/t',
ROWTERMINATOR='/n'
)
前提条件是表如kind要存在,而且字段与test.txt字段数目要一样多
在查询分析器中运行,而且是以sa用户登录
bulkinserttbyhhkfrom'c:/t.txt'with(formatfile='c:/bcp.txt')
3.其它数据库的导入方法:
(1)--/*dBaseIV文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIV;HDR=NO;IMEX=2;DATABASE=C:/','select*from[客户资料4.dbf]')
--*/
(2)--/*dBaseIII文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIII;HDR=NO;IMEX=2;DATABASE=C:/','select*from[客户资料3.dbf]')
--*/
(3)--/*数据库
select*fromopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:/',
'select*from[aa.DBF]')
--*/
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
导入导出大全(2004-12-13)
http://builderman.home.sunbo.net/show_hdr.php?xname=U53DTV0&dname=517DTV0&xpos=1
一、打开另外一个
1.
打开SQLserver2000的另一个数据库的表
(1). select*fromopenrowset('sqloledb','sprogram';'develop';'11111','select*fromkind')
(2). select*fromopenrowset('sqloledb','190.1.1.247';'develop';'11111','select*fromkind')
(3). select*fromopendatasource('sqloledb','datasource=sprogram;userID=develop;Password=11111').new_ks.dbo.kind
2.
打开2000的一个表
(1). select* fromOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource=e:/north.mdb;UserID=Admin;Password=')...supply
(2). select*fromopenrowset('Microsoft.Jet.OLEDB.4.0',
'e:/north.mdb';'admin';'',supply)
(3). select*fromopenrowset('Microsoft.Jet.OLEDB.4.0',
'e:/north.mdb';'admin';'','select*fromsupplywhere供应商编号>10')
3.
打开的一个表
(1).select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=e:/test.xls',sheet1$)
(2).select*into表fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=e:/test1.xls',sheet3$)
二、导入与导出
1. 用BCP工具导入导出
(1)xls文件
EXECmaster..xp_cmdshell'bcp"SELECTclass_no,kind_no,cn_nameFROMnew_ks.dbo.kindORDERBY1,2"queryout"e:/test3.xls"-c-q-S"sprogram"-U"develop"-P"12345"'
(2)/**导入文本文件
--入SQLServer
select* into你的表fromOpenRowset('MSDASQL','Driver={MicrosoftTextDriver(*.txt;*.csv)};DefaultDir=c:/temp;','select*fromcontact.txt')
--查询导出
EXECmaster..xp_cmdshell'bcp"SELECTclass_no,kind_no,cn_nameFROMnew_ks.dbo.kindORDERBY1,2"queryout"e:/TX1.txt"-c-q-S"sprogram"-U"develop"-P"12345"'
--直接导出
EXECmaster..xp_cmdshell'bcpnew_ks.dbo.classout"e:/TX3.txt"-c-S"sprogram"-U"develop"-P"12345"'
--直接导入
EXECmaster..xp_cmdshell'bcp"数据库.dbo.数据表"inc:/DT.txt-c-S服务器名-U用户-P密码'
EXECmaster..xp_cmdshell'bcphdh.dbo.kindin"e:/TX1.txt"-c-q-S-U-P'
EXECmaster..xp_cmdshell'bcphdh.dbo.kindin"e:/test3.xls"-c-q-S-U-P'
--用BULKINSERT导入
BULKINSERThdh.dbo.kind
FROM'e:/tx1.txt'
WITH(
FIELDTERMINATOR='/t',
ROWTERMINATOR='/n'
)
前提条件是表如kind要存在,而且字段与test.txt字段数目要一样多
在查询分析器中运行,而且是以sa用户登录
bulkinserttbyhhkfrom'c:/t.txt'with(formatfile='c:/bcp.txt')
3.其它数据库的导入方法:
(1)--/*dBaseIV文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIV;HDR=NO;IMEX=2;DATABASE=C:/','select*from[客户资料4.dbf]')
--*/
(2)--/*dBaseIII文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIII;HDR=NO;IMEX=2;DATABASE=C:/','select*from[客户资料3.dbf]')
--*/
(3)--/*数据库
select*fromopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:/',
'select*from[aa.DBF]')
--*/
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>