С龃笕(2004-12-13)_sqlserver

С龃笕(2004-12-13)

http://builderman.home.sunbo.net/show_hdr.php?xname=U53DTV0&dname=517DTV0&xpos=1

http://www.gaodaima.com/34692.htmlС龃笕(2004-12-13)_sqlserver

一、打_另外一

1.

打_SQL server2000的另一的表

(1).  select * from openrowset(‘sqloledb’,’sprogram’;’develop’;’11111′,’select * from kind’)

(2).  select * from openrowset(‘sqloledb’,’190.1.1.247′;’develop’;’11111′,’select * from kind’)

(3).  select * from opendatasource(‘sqloledb’,’data source=sprogram;user ID=develop;Password=11111′).new_ks.dbo.kind

2.

打_access2000的一表

(1).  select *  from OPENDATASOURCE( ‘Microsoft.Jet.OLEDB.4.0′,’Data Source=e:/north.mdb;User ID=Admin;Password=’)…supply

(2).  select * from openrowset(‘Microsoft.Jet.OLEDB.4.0’,

   ‘e:/north.mdb’;’admin’;”,supply)

(3).  select * from openrowset(‘Microsoft.Jet.OLEDB.4.0’,

   ‘e:/north.mdb’;’admin’;”,’select * from supply where 供叹>10′)

3.

打_Excel的一表

(1). select * from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’

,’Excel 5.0;HDR=YES;DATABASE=e:/test.xls’,sheet1$)

(2). select * into 表 from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’

，’Excel 5.0;HDR=YES;DATABASE=e:/test1.xls’，sheet3$)

二、肱cС

1.      用BCP工具С

(1)xls文件

EXEC master..xp_cmdshell ‘bcp “SELECT class_no，kind_no，cn_name FROM new_ks.dbo.kind ORDER BY 1，2” queryout “e:/test3.xls” -c -q -S”sprogram” -U”develop” -P”12345″‘

(2)/** 导入文本文件

–入SQLServer

select *  into 你的表 from OpenRowset(‘MSDASQL’， ‘Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:/temp;’，’select * from contact.txt’)

–查С

EXEC master..xp_cmdshell ‘bcp “SELECT class_no，kind_no，cn_name FROM new_ks.dbo.kind ORDER BY 1，2” queryout “e:/TX1.txt” -c -q -S”sprogram” -U”develop” -P”12345″‘

–直接С

EXEC master..xp_cmdshell ‘bcp new_ks.dbo.class out “e:/TX3.txt” -c -S”sprogram” -U”develop” -P”12345″‘

–直接

EXEC master..xp_cmdshell ‘bcp “.dbo.” in c:/DT.txt -c -S服掌髅 -U用 -P密a’

EXEC master..xp_cmdshell ‘bcp hdh.dbo.kind in “e:/TX1.txt” -c -q -S -U -P’

EXEC master..xp_cmdshell ‘bcp hdh.dbo.kind in “e:/test3.xls” -c -q -S -U -P’

–用BULK INSERT

BULK INSERT hdh.dbo.kind

FROM ‘e:/tx1.txt’

WITH (

    FIELDTERMINATOR = ‘/t’，

    ROWTERMINATOR = ‘/n’

)

前提l件是表如kind要存在，而且字段ctest.txt字段的恳佣

在查分析器中/行，而且是以sa用舻卿

bulk insert tbyhhk from ‘c:/t.txt’ with( formatfile=’c:/bcp.txt’)

3.其它的敕椒ǎ

(1)–/* dBase IV文件
select * from 
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’
，’dBase IV;HDR=NO;IMEX=2;DATABASE=C:/’，’select * from [客户资料4.dbf]’)
–*/
(2)–/* dBase III文件
select * from 
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’
，’dBase III;HDR=NO;IMEX=2;DATABASE=C:/’，’select * from [客户资料3.dbf]’)
–*/
(3)–/* FoxPro 数据库
select * from openrowset(‘MSDASQL’，
‘Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’，
‘select * from [aa.DBF]’)
–*/

欢迎大家阅读《С龃笕(2004-12-13)_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)