Recycle手札: SQL

顯示具有 SQL 標籤的文章。顯示所有文章

2015年6月15日星期一

JOIN與TOP1並用

有新的JOIN方式，但僅適用於2005以後的版本。

舊的寫法用SUB SELECT寫法會Timeout，新的只需17秒。

SELECT *
FROM FDRPSSDI AS D
OUTER APPLY (
SELECT TOP 1 NEWCARD, NEWPRI
FROM (
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
LEFT JOIN FDRCAMD2 AS C2 ON M2.PROJNO = C2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '1'
AND C2.CUNO = '0018'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO1
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND <> 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '2'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND <> 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
LEFT JOIN FDRCAMD2 AS C2 ON M2.PROJNO = C2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '1'
AND C2.CUNO = '0018'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO1
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND = 'Y'
UNION ALL
SELECT M2.STDATE, M2.ORDNO, D2.NEWCARD, D2.NEWPRI
FROM FDRCAMD1 AS D2
LEFT JOIN FDRCAMMI AS M2 ON M2.PROJNO = D2.PROJNO
WHERE 1 = 1
AND M2.SUITGONO = '2'
AND M2.STDATE <= '20150601'
AND D2.GONO = D.GONO
AND M2.YNSTATE = 'Y'
AND M2.NEVEREND = 'Y'
) AS A
ORDER BY STDATE DESC, ORDNO) AS B
WHERE 1 = 1
AND D.CUNO = '0018'
AND D.PDATE = '20150601'
AND ISNULL(D.CARD2, '') = ''

2014年10月21日星期二

PRIMARY 檔案群組已滿

--先找出DB的邏輯名稱
sp_helpdb DateBaseName
sp_helpfile

--DB的屬性
alter database DateBaseName
modify file
(
name='edwinbk_100707_log'
,maxsize=UNLIMITED
--,maxsize=42gb
,FILEGROWTH=10%
)

2013年11月13日星期三

取得、新增、更新MS SQL中資料欄位的描述內容

--List DBNAME
select name from master.dbo.sysdatabases

--List Table name
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable
FROM sys.tables

--List Column Property
SELECT a.Table_schema +'.'+a.Table_name   as 表格名稱
       ,b.COLUMN_NAME                             as 欄位名稱
       ,b.DATA_TYPE                                    as 資料型別
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 長度
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值
       ,b.IS_NULLABLE                                 as 允許空值
       ,( SELECT value
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)
          WHERE name='MS_Description' and objtype='COLUMN'
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
        ) as 欄位描述
FROM INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE' and a.Table_name='code'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION

--新增或修改資料表說明

IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', NULL, NULL))
BEGIN
exec sp_addextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱'
END
ELSE
BEGIN
exec sp_updateextendedproperty 'MS_Description', '資料表說明', 'user', 'dbo', 'table', '資料表名稱'
END

--新增或修改欄位說明

IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'))
BEGIN
exec sp_addextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'
END
ELSE
BEGIN
exec sp_updateextendedproperty 'MS_Description', '欄位說明', 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱'
END

::fn_listextendedproperty用法

--取得資料表單一欄位說明

SELECT value FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', '欄位名稱')

--取得資料表所有欄位說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', 'column', NULL)

--取得資料表說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '資料表名稱', NULL, NULL)

--取得所有資料表說明

SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', NULL, NULL, NULL)

--上面使用NULL的參數,可以改用default 代替

SELECT * FROM ::fn_listextendedproperty(default, 'user', 'dbo', 'table', default, default, default)

2013年6月20日星期四

如何指定View中的欄位定序

1.確認目前資料庫定序

SELECT DATABASEPROPERTYEX ('ricotest1' ,'Collation' )

CI: 指定不區分大小寫，CS 指定區分大小寫。
AI 指定不區分腔調字，AS 指定區分腔調字。
2.Create Source Table and Insert Data

create table mytest

c1 varchar(10),

c2 varchar(10)

insert mytest select 'rico','123 abc'

insert mytest select 'ricochen','456 def'

insert mytest select 'RICOCHEN','456 DEF'

insert mytest select 'RICO','123 ABC'

select * from mytest

查詢 Table (c1=rico ,c2 like '%def')

select * from mytest

where c1='rico'

go

select * from mytest

where c2 like '%def'

go

因為來源c1和c2欄位定序為不區分大小寫。
3.我希望在View中c1和c2兩個欄位都可以區分大小寫

create view dbo.v_mytest

as

SELECT

c1 COLLATE Chinese_Taiwan_Stroke_CS_AS AS c1_CS,

c2 COLLATE Chinese_Taiwan_Stroke_CS_AS AS c2_CS

FROM dbo.mytest;

4.查詢View (c1=rico ,c2 like '%def')
這次查詢的結果才是我要的。

2012年8月8日星期三

整理索引碎片，提升SQL Server速度

轉載自整理索引碎片，提升SQL Server速度

資料庫表A有十萬條記錄，查詢速度本來還可以，但導入一千條資料後，問題出現了。當選擇的資料在原十萬條記錄之間時，速度還是挺快的；但當選擇的資料在這一千條資料之間時，速度變得奇慢。
憑經驗，這是索引碎片問題。檢查索引碎片DBCC SHOWCONTIG（表），得到如下結果：
DBCC SHOWCONTIG 正在掃描 'A' 表……
表： 'A'（884198200）；索引 ID： 1，資料庫 ID： 13
已執行 TABLE 級別的掃描。
- 掃描頁數……： 3127
- 掃描擴展盤區數……： 403
- 擴展盤區開關數……： 1615
- 每個擴展盤區上的平均頁數……： 7.8
- 掃描密度[最佳值：實際值]……： 24.20%[391：1616]
- 邏輯掃描碎片……： 68.02%
- 擴展盤區掃描碎片……： 38.46%
- 每頁上的平均可用位元組數……： 2073.2
- 平均頁密度（完整）……： 74.39%
DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯繫。
由上我們看出，邏輯掃描碎片和擴展盤區掃描碎片都非常大，果真需要對索引碎片進行處理了。
一般有兩種方法解決，一是利用DBCC INDEXDEFRAG整理索引碎片，二是利用DBCC DBREINDEX重建索引。二者各有優缺點。調用微軟的原話如下：
DBCC INDEXDEFRAG 命令是線上作業，所以索引只有在該命令正在運行時才可用。而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織資料方面沒有聚集索引的除去/重新創建操作有效。
重新創建聚集索引將對資料進行重新組織，其結果是使資料頁填滿。填滿程度可以使用 FILLFACTOR 選項進行配置。這種方法的缺點是索引在除去/重新創建週期內為脫機狀態，並且操作屬原子級。如果中斷索引創建，則不會重新創建該索引。
也就是說，要想獲得好的效果，還是得用重建索引，所以決定重建索引。
DBCC DBREINDEX（表，索引名，填充因數）
第一個參數，可以是表名，也可以是表ID.
第二個參數，如果是''，表示影響該表的所有索引。
第三個參數，填充因數，即索引頁的資料填充程度。如果是100，表示每一個索引頁都全部填滿，此時select效率最高，但以後要插入索引時，就得移動後面的所有頁，效率很低。如果是0，表示使用先前的填充因數值。
DBCC DBREINDEX（A，''，100）
重新測試查詢速度，飛快。
另：一般來說填充因數使用系統默認的值即可。

索引重整與效率

轉載自索引重整與效率

剛接觸程式設計工作時，只知道設定索引是一件很重要的事
依著前輩的指點，囫圇吞棗般只會把常query或是關鍵欄位設定為索引...
直到一次，不知情的同事將一個近百萬筆資料table的欄位中，
連包含非key值的長字串資料欄位全都設為索引之後....
這動作一下..效能盡失，最後嚴重拖垮整個資料庫服務
此時，我才知道「會不會」設索引，才是一件重要的事~~~
最近利用DBCC SHOWCONTIG這指令去觀查幾個資料量較多的table
在觀察數據的同時，對於DBCC INDEXDEFRAG和DBCC DBREINDEX兩指令又有一新認知。
step1.執行 DBCC SHOWCONTIG(my_table)
單純針對table進行層級掃描先取得分頁相關資訊
- 掃描的分頁................................: 64
- 掃描的範圍..............................: 11
- 範圍切換..............................: 12
- 每個範圍的平均分頁........................: 5.8
- 掃描密度 [最佳次數:實際次數].......: 33.54% [4:13]
- 邏輯掃描片段 ..................: 53.13%
- 範圍掃描片段 ...................: 36.36%
- 每個分頁的平均可用位元組.....................: 376.5
- 平均分頁密度 (全滿).....................: 95.35%
step2.執行 DBCC INDEXDEFRAG (MyDB, 'my_table',PK_my_table)
執行破碎頁面的整理後，掃描密度明顯地變大，分頁程度變得更緊密更平均。
邏輯掃描片段(cluster index叢集索引的葉層分頁狀況)愈大表示指標的指向愈紊亂，
也就是說「失序」頁面的比例，重整破碎頁面後確實有得到明顯的效果。
- 掃描的分頁................................: 63
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.0
- 掃描密度 [最佳次數:實際次數].......: 88.89% [8:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 44.44%
- 每個分頁的平均可用位元組.....................: 254.0
- 平均分頁密度 (全滿).....................: 96.86%
step3.執行 DBCC DBREINDEX (my_table,'' ,90)
執行索引的重整，並依需求重新設定填滿因子
(這個TABLE 查詢的比例多，資料異動機率並不是很頻繁，因此設為90%)
發現掃描密度、範圍掃描片段、分頁平均可用位元組得到更多的改善空間。
- 掃描的分頁................................: 68
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.6
- 掃描密度 [最佳次數:實際次數].......: 100.00% [9:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 0.00%
- 每個分頁的平均可用位元組.....................: 830.6
- 平均分頁密度 (全滿).....................: 89.74%
後記：
索引重整就像梳頭髮一樣，原本雜亂的頭髮愈梳他就會愈順
定期整理這些資料量大的table，讓index分頁的b-tree目錄在搜尋資料時發揮應有的效能，
索引一旦建立之後，應該更需要去維護它，放著不管最終反而變成是影響效能的因素之一。
但是索引並不是完全只有好處，異動頻繁的table除了不要建立太多的索引之外
對於索引的填滿率(填滿因子)更需注意，保留適當的空間，讓新刪修頻繁的table展現最好的效益。
微軟的官方文件中也有提到....
下一版的 Microsoft SQL Server 將不再提供此功能。
請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。請改用 ALTER INDEX。

2012年3月7日星期三

SQL 2005以後，已有ROWNO指令可用

以前我們在寫報表的時候，遇到查詢結果要排名次，因為資料庫ms sql 2000不支援 (oracle早就有這個屬性了)，所以無法產生列號，所以只能自己在產生出來之後，再跑迴圈去寫入列號。

其實在2005的版本以後，已經有這個屬性了，未來可利用這個方式來撰寫，就少一道在程式裡面撰寫的麻煩了，不過這個方法，僅限在ms sql，如果要多db支援的話，那就必須自行轉換sql語法了。

範例：以北風資料庫裡面的訂單，要取得前99筆的語法。

select top 100 * from (
select [order details].*,orders.customerid
,ROW_NUMBER() over (order by [order details].orderid,productid) as r
from [order details]
left join orders on [order details].orderid = orders.orderid
) as o where r > 100
order by r

轉檔產生序號進階版

以往轉單據時，有時候原始資料並未存在單據的每一個項目序號，這時候，我們就要自行產生，以往的方式就是對原始資料下count(*)，這時候其實效能並不好。

利用Identity語法轉到記憶體(暫存table)裡面，可以省下不少時間。

--轉單據明細序號自動產生作法進階版(以90萬筆為例)

/*此作法一定不會重覆KEY值，而且快速*/

--先將所有資料寫入暫存TABLE，此時可以產生種子序號(Identity)

SELECT identity(int) AS ID0, * INTO #TMP_FDRSALDI

FROM FDRSALDI

--暫存檔依序號排

SELECT * FROM #TMP_FDRSALDI ORDER BY ID0

--得到最後的結果，此結果可直接寫入要轉入的目標TABLE

SELECT SHID, SHNO, (SELECT COUNT(*) FROM #TMP_FDRSALDI WHERE SHNO = D.SHNO AND ID0 <= D.ID0) AS SRNO2
, WARE, GONO, GONO1, GONO2, QTY, PRICE, AMT
FROM #TMP_FDRSALDI D
ORDER BY SHNO, SRNO2

2011年8月26日星期五

SQL2008 LOCK解決方案

--顯示被lock之序號

SP_LOCK

--刪除LOCK spid

KILL spid

--列出最初鎖住資源，導致一連串其它處理序被鎖住的起始源頭
IF EXISTS(SELECT * FROM master.dbo.sysprocesses WHERE spid in (SELECT blocked FROM master.dbo.sysprocesses))
BEGIN
SELECT spid 處理序, status 狀態
, 登入帳號=SUBSTRING(SUSER_SNAME(sid), 1, 30)
, 使用者機器名稱=SUBSTRING(hostname, 1, 12)
, 是否被鎖住=CONVERT(char(3), blocked)
, 資料庫名稱=SUBSTRING(DB_NAME(dbid), 1, 10)
, cmd 命令, waittype 等待狀態
FROM master.dbo.sysprocesses
--列出鎖住別(在別的處理序中，blocked欄位出現的值)，但自己未被鎖住(blocked=0)
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
END
ELSE
BEGIN
SELECT '沒有處理序被鎖住'
END

--開啟進階顯示(SQL2008以後需開啟)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

--取消平行處理
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE WITH OVERRIDE
GO

2011年7月22日星期五

依斜線拆解日期欄位

--依斜線拆解日期欄位
SELECT REDATE,* FROM
(
SELECT SUBSTRING(REDATE,1,POS1-1) AS YYYY
,SUBSTRING(REDATE,POS1+1,POS2-1) AS MM
,SUBSTRING(REDATE,POS1+POS2+1,3) AS DD
,*
FROM
(
    --取出第一個斜線位置
    SELECT CHARINDEX('/',REDATE) AS POS1
    --取出第二個斜線位置
    ,CHARINDEX('/',SUBSTRING(REDATE,CHARINDEX('/',REDATE)+1,10)) AS POS2
    ,* FROM FDRDMDMI_TMP
) TA
WHERE ISNULL(KIND,'')<>'Y' AND POS1 >0
) TB

--檢查日期正確性
SELECT CAST (REDATE AS DATETIME),
REDATE,COSNO,VIPNO,NAME
FROM FDRDMDMI_TMP
WHERE ISNULL(KIND,'')<>'Y'

--找出錯誤日期格式
DECLARE @iCNT INT
SET @ICNT=8170
EXEC('SELECT TOP '+@ICNT+' REDATE,*FROM FDRDMDMI_TMP WHERE ISNULL(KIND,'''')<>''Y''')

--年份有錯之資料作上註記
UPDATE FDRDMDMI_TMP SET KIND='Y'
WHERE REDATE IN(
SELECT REDATE FROM
(
    SELECT SUBSTRING(REDATE,1,POS1-1) AS YYYY
    ,SUBSTRING(REDATE,POS1+1,POS2-1) AS MM
    ,SUBSTRING(REDATE,POS1+POS2+1,3) AS DD
    ,*
    FROM
    (
      --取出第一個斜線位置
      SELECT CHARINDEX('/',REDATE) AS POS1
      --取出第二個斜線位置
      ,CHARINDEX('/',SUBSTRING(REDATE,CHARINDEX('/',REDATE)+1,10)) AS POS2
      ,* FROM FDRDMDMI_TMP
    ) TA
    WHERE ISNULL(KIND,'')<>'Y' AND POS1 >0
) TB
WHERE LEN(YYYY)>4
)

2011年5月18日星期三

轉換腔調字

如題：簡/繁體依腔調字做定序，若定序不一致，JOIN資料表時會有問題

簡體建立資料表時需指定腔調字

--繁體轉簡體

ALTER TABLE FDRCALSI

ALTER COLUMN RECMAN varchar(10) COLLATE Chinese_PRC_CI_AS

2011年3月21日星期一

在SQL內用參數傳入執行(以備份資料庫為範例)

--將自動備份寫成PROCEDURE
CREATE PROCEDURE DBDACKUP AS
DECLARE @BACKDATE VARCHAR(8)
DECLARE @ACOMMAND VARCHAR(200)
--備份日期
SET @BACKDATE = CONVERT(VARCHAR(8), GETDATE(), 112)
--備份指令
SET @ACOMMAND =
'BACKUP DATABASE [QRSP_HM] TO DISK = N''D:\databak\QRSP_HM_backup_'+@BACKDATE+'.bak'''
+'WITH NOFORMAT, NOINIT, NAME = N''QRSP_HM_backup_'+@BACKDATE+'.bak'''+', SKIP, REWIND, NOUNLOAD, STATS = 10'
--執行備份指令
--因指令內有包含變數(@BACKDATE)，所以需用EXEC方式執行
EXEC(@ACOMMAND)

--刪除備份檔(保留21天)
SET @BACKDATE = CONVERT(VARCHAR(8), GETDATE()-21, 112)
SET @ACOMMAND = 'MASTER.dbo.xp_delete_file 0,N''D:\databak'',N''BAK'',N'''+@BACKDATE+''''
EXEC(@ACOMMAND)

2011年3月1日星期二

如何取出輸入資料的前N天金額平均值

以下SQL作法，可以取出資料前6天金額加總
前6天是指有輸入的資料往前推6天

SELECT DATE,SUM(SALAMT) AS SALAMT,S.CUNO
--取出前6天之金額平均值
,(SELECT AVG(SALAMT) FROM FDRSALSI S1 WHERE S1.CUNO=S.CUNO AND DATE
IN(SELECT TOP 6 DATE FROM FDRSALSI S2 WHERE S2.CUNO=S.CUNO
AND S2.DATE <S.DATE GROUP BY DATE,CUNO ORDER BY DATE DESC)
) AS AVGAMT
FROM FDRSALSI S
WHERE DATE='20100211'
GROUP BY DATE,S.CUNO

2011年2月13日星期日

前N筆平均值‏

以下SQL做法可以取出當筆資料前N筆(不含本筆資料)的平均值

當然 SUM(CALAMT)/COUNT(*)可以用AVG(CALAMT)取代

SELECT (SELECT SUM(CALAMT)/COUNT(*),* FROM (

(SELECT TOP 2 * FROM TABLE_NAME WHERE CUNO = T.CUNO AND DATE < T.DATE ORDER BY DATE DESC )

) AS A) AS AVGAMT

FROM TABLE_NAME T

WHERE 1=1

2010年11月23日星期二

SQL 查詢換行符號

where id like '%'+char(10)+'%'

CHAR 可用於將控制字符插入字符串中。下表顯示了一些常用的控制字符。
控制鍵值
Tab： CHAR(9)
換行： CHAR(10)
ENTER： CHAR(13)

Ex:SELECT * FROM TABLE_NAME
WHERE 1=1 AND COLUMN_NAME LIKE '%'+CHAR(13)+'%'

2010年8月1日星期日

SQL語句導入導出大全[轉載]

以下文章轉錄自：SQL server匯入 Access檔

USE test
GO
SELECT * INTO zone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'd:\test\test.mdb';'admin';'',filename)

匯入Excel檔

SELECT * INTO zone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test\data.xls', [sheetname$])

/******* 導出到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 [客戶資料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',
'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表)

文件名為參數
declare @fname varchar(20)
set @fname = 'd:\test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')...產品

********************* 導入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
      Customer was very satisfied
            Important
            Happy Customer.
'
-- 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

???????

/**********************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實現的存儲過程

/*
實現數據導入/導出的存儲過程
根據不同的參數,可以實現導入/導出整個數據庫/單個表
調用示例:
--導出調用示例
----導出單個表
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

/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO

delete from openquery(mailser,'select * from yulin')

select * from openquery(mailser,'select * from yulin')

update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)

補充：

對於用bcp導出,是沒有字段名的.

用openrowset導出,需要事先建好表.

用openrowset導入,除ACCESS及EXCEL外,均不支持非本機數據導入