SQLÓï¾äµ¼Èëµ¼³ö

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
/*¶¯Ì¬ÎļþÃû
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * from OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/
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
/********************** EXCELµ¼µ½Ô¶³ÌSQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=Ô¶³Ìip;User ID=sa;Password=ÃÜÂë'
).¿âÃû.dbo.±íÃû (ÁÐÃû1,ÁÐÃû2)
SELECT ÁÐÃû1,ÁÐÃû2
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 [¿Í»§×Ê