建站
咨詢
售后
建站咨詢
新聞中心
Sybase ASE15.0之前的版本中利用bcp這個(gè)實(shí)用程序只能夠?qū)С稣砘蛞晥D的數(shù)據(jù)。要想利用bcp有條件得導(dǎo)出表內(nèi)數(shù)據(jù)，只能根據(jù)條件建立視圖，然后再導(dǎo)出該視圖的數(shù)據(jù)；或者根據(jù)條件建立臨時(shí)表，再導(dǎo)出臨時(shí)表的數(shù)據(jù)。但是，都必須在執(zhí)行bcp命令之前到數(shù)據(jù)庫內(nèi)部去創(chuàng)建對象（視圖或者臨時(shí)表），然后再執(zhí)行bcp命令導(dǎo)出視圖或者臨時(shí)表數(shù)據(jù)。顯然，不是太方便。而其它的數(shù)據(jù)庫管理系統(tǒng)，比如sqlserver早在sqlserver2000就實(shí)現(xiàn)了根據(jù)條件queryout數(shù)據(jù)的功能。利用sybase ASE的第三方工具按條件導(dǎo)出數(shù)據(jù)也是一種辦法。
成都創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站建設(shè)、做網(wǎng)站與策劃設(shè)計(jì),拉孜網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:拉孜等地區(qū)。拉孜做網(wǎng)站價(jià)格咨詢:18980820575
在Sybase ASE 15.0及以后版本中，sybase終于提供了按照條件導(dǎo)出表內(nèi)數(shù)據(jù)的辦法，雖然實(shí)現(xiàn)起來還稍微有一點(diǎn)點(diǎn)小麻煩，但是終究是有這個(gè)功能了。參數(shù)：--initstring 就是為實(shí)現(xiàn)這個(gè)功能而加的。
關(guān)于參數(shù)：--initstring的注意事項(xiàng)有：
1. 參數(shù)--initstring之后的SQL語句會在數(shù)據(jù)導(dǎo)出之前被發(fā)送到ASE引擎；
2. 參數(shù)--initstring之后的SQL語句被當(dāng)做會話級別的SQL語句一樣處理；
3. 參數(shù)--initstring之后的SQL語句在整個(gè)bcp導(dǎo)出數(shù)據(jù)會話期間始終有效；
4. 真正導(dǎo)出的數(shù)據(jù)是關(guān)鍵字bcp和out之間的表的數(shù)據(jù)，而不是參數(shù)--initstring中select列表的數(shù)據(jù)。
下面開始舉幾個(gè)例子:
bcp導(dǎo)出的是關(guān)鍵字bcp和out之間的表的數(shù)據(jù)，而不是--initstring中select列表的內(nèi)容
bcp master..sysobjects out sysobjects.txt --initstring "select id,name,type from master..sysobjects where type='U' " -c -Usa -P -Stest
- C:\Documents and Settings\Administrator>bcp master..sysobjects out sysobjects.tx
- t --initstring "select id,name,type from master..sysobjects where type='U' " -c
- -Usa -P -Stest
- Starting copy...
- 163 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (10187.50 rows per sec.)
- C:\Documents and Settings\Administrator>more sysobjects.txt
- sysobjects 1 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysindexes 2 1 S 0 97 0 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- syscolumns 3 1 S 0 97 0 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- systypes 4 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- syslogs 8 1 S 0 1 0 0 73728 Dec 2 2
- 009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0 0
- 0 0 0 0 0
- syspartitions 28 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysgams 14 1 S 0 1 0 0 73728 Dec 2 2
- 009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0 0
- 0 0 0 0 0
- systabstats 23 1 S 0 97 1 0 229888
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysusages 31 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysdatabases 30 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysdevices 35 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- ^C
- C:\Documents and Settings\Administrator>
- C:\Documents and Settings\Administrator>bcp master..sysobjects out sysobjects.tx
- t --initstring "select id,name,type from master..sysobjects where type='U' " -c
- -Usa -P -Stest
- Starting copy...
- 163 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (10187.50 rows per sec.)
- C:\Documents and Settings\Administrator>more sysobjects.txt
- sysobjects 1 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysindexes 2 1 S 0 97 0 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- syscolumns 3 1 S 0 97 0 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- systypes 4 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- syslogs 8 1 S 0 1 0 0 73728 Dec 2 2
- 009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0 0
- 0 0 0 0 0
- syspartitions 28 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysgams 14 1 S 0 1 0 0 73728 Dec 2 2
- 009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0 0
- 0 0 0 0 0
- systabstats 23 1 S 0 97 1 0 229888
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysusages 31 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysdatabases 30 1 S 0 97 2 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- sysdevices 35 1 S 0 97 1 0 229376
- Dec 2 2009 6:58:34:590PM Dec 2 2009 6:58:34:590PM 0 0
- 0 0 0 0 0 0 1
- 0000000000000000
- ^C
- C:\Documents and Settings\Administrator>
上面的這個(gè)例子中，sql語句select id,name,type from master..sysobjects where type='U' 雖然被執(zhí)行了， 但是并沒有被反映到導(dǎo)出的結(jié)果中。
想導(dǎo)出sysobjects表中用戶表的id和name兩列數(shù)據(jù)，可以利用臨時(shí)表和視圖來實(shí)現(xiàn)。但是這和ase15以前版本中的臨時(shí)表和視圖還是不一樣的。ase15之前版本中臨時(shí)表或者視圖是顯示創(chuàng)建的，而在ase15及后續(xù)版本中可以利用隱式創(chuàng)建的臨時(shí)表或者視圖來實(shí)現(xiàn)根據(jù)條件導(dǎo)出數(shù)據(jù)的要求。
方法一: 利用隱式創(chuàng)建的臨時(shí)表來由條件導(dǎo)出數(shù)據(jù)
要求是：導(dǎo)出sysobjects表中用戶表的id和name兩列數(shù)據(jù)。bcp命令語句如下：
- bcp #temptbl out sysobjects_id_name.txt
- --initstring "select id,name into #temptbl
- from sysobjects where type='U' order by name " -c -Usa -P -Stest
效果如下：
- C:\Documents and Settings\Administrator>bcp #temptbl out sysobjects_id_name.txt
- --initstring "select id,name into #temptbl from sysobjects where type='U' order
- by name " -c -Usa -P -Stest
- Starting copy...
- 87 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (5437.50 rows per sec.)
- C:\Documents and Settings\Administrator>more sysobject_id_name.txt
- 233048835 HP_PAGENO_RANGE
- 1065051799 IVCMPLX
- 553049975 IVCON
- 601050146 IVCPROP
- 425049519 IVONEROW
- 793050830 IVPARAM
- 377049348 IVSCHM
- 457049633 IVSCON
- 937051343 IVSHARE
- 889051172 IVSPROC
- 649050317 IVSPROP
- 1033051685 IVSRVR
- 505049804 IVSSYN
- 985051514 IVSTAT
- 841051001 IVSTR
- 697050488 IVSVIEW
- 745050659 IVVCOL
- 121048436 errorlog
- 1337052768 ijdbc_function_escapes
- 1093575903 jdbc_function_escapes
- 265048949 lzflzf
- 21572084 monCachePool
- 2137055618 monCachedObject
- 533573908 monCachedProcedures
- 725574592 monCachedStatement
- 1753054250 monDataCache
- 2041055276 monDeadLock
- 117572426 monDeviceIO
- 1721054136 monEngine
- 1977055048 monErrorLog
- 85572312 monIOQueue
- 661574364 monLicense
- 2009055162 monLocks
- 1945054934 monNetworkIO
- 1881054706 monOpenDatabases
- 53572198 monOpenObjectActivity
- 629574250 monOpenPartitionActivity
- 757574706 monPCIBridge
- 821574934 monPCIEngine
- 789574820 monPCISlots
- 1785054364 monProcedureCache
- 1817054478 monProcedureCacheMemoryUsage
- ^C
- C:\Documents and Settings\Administrator>
- C:\Documents and Settings\Administrator>bcp #temptbl out sysobjects_id_name.txt
- --initstring "select id,name into #temptbl from sysobjects where type='U' order
- by name " -c -Usa -P -Stest
- Starting copy...
- 87 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (5437.50 rows per sec.)
- C:\Documents and Settings\Administrator>more sysobject_id_name.txt
- 233048835 HP_PAGENO_RANGE
- 1065051799 IVCMPLX
- 553049975 IVCON
- 601050146 IVCPROP
- 425049519 IVONEROW
- 793050830 IVPARAM
- 377049348 IVSCHM
- 457049633 IVSCON
- 937051343 IVSHARE
- 889051172 IVSPROC
- 649050317 IVSPROP
- 1033051685 IVSRVR
- 505049804 IVSSYN
- 985051514 IVSTAT
- 841051001 IVSTR
- 697050488 IVSVIEW
- 745050659 IVVCOL
- 121048436 errorlog
- 1337052768 ijdbc_function_escapes
- 1093575903 jdbc_function_escapes
- 265048949 lzflzf
- 21572084 monCachePool
- 2137055618 monCachedObject
- 533573908 monCachedProcedures
- 725574592 monCachedStatement
- 1753054250 monDataCache
- 2041055276 monDeadLock
- 117572426 monDeviceIO
- 1721054136 monEngine
- 1977055048 monErrorLog
- 85572312 monIOQueue
- 661574364 monLicense
- 2009055162 monLocks
- 1945054934 monNetworkIO
- 1881054706 monOpenDatabases
- 53572198 monOpenObjectActivity
- 629574250 monOpenPartitionActivity
- 757574706 monPCIBridge
- 821574934 monPCIEngine
- 789574820 monPCISlots
- 1785054364 monProcedureCache
- 1817054478 monProcedureCacheMemoryUsage
- ^C
- C:\Documents and Settings\Administrator>
方法二: 利用隱式創(chuàng)建的視圖來由條件導(dǎo)出數(shù)據(jù)
- bcp master..V_sysobjects_id_name out V_sysobjects_id_name.txt
- --initstring " create view V_sysobjects_id_name as
- select id,name from sysobjects where type='U' " -c -Usa -P -Stest
效果如下：
- C:\Documents and Settings\Administrator>bcp master..V_sysobjects_id_name out V_s
- ysobjects_id_name.txt --initstring " create view V_sysobjects_id_name as select
- id,name from sysobjects where type='U'" -c -Usa -P -Stest
- Starting copy...
- 87 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (5437.50 rows per sec.)
- C:\Documents and Settings\Administrator>more V_sysobjects_id_name.txt
- 1097051913 spt_values
- 1129052027 spt_monitor
- 1177052198 spt_limit_types
- 1088003876 syblicenseslog
- 1225052369 spt_ijdbc_table_types
- 1257052483 spt_ijdbc_mda
- 1305052654 spt_ijdbc_conversion
- 1337052768 ijdbc_function_escapes
- 1593053680 monTables
- 1625053794 monTableParameters
- 1657053908 monTableColumns
- 1689054022 monState
- 1721054136 monEngine
- 1753054250 monDataCache
- 1785054364 monProcedureCache
- 1817054478 monProcedureCacheMemoryUsage
- 1849054592 monProcedureCacheModuleUsage
- 1881054706 monOpenDatabases
- 1913054820 monSysWorkerThread
- 1945054934 monNetworkIO
- 1977055048 monErrorLog
- 2009055162 monLocks
- 2041055276 monDeadLock
- 2073055390 monWaitClassInfo
- 2105055504 monWaitEventInfo
- 2137055618 monCachedObject
- 21572084 monCachePool
- 53572198 monOpenObjectActivity
- 85572312 monIOQueue
- 117572426 monDeviceIO
- 149572540 monSysWaits
- 181572654 monProcess
- 213572768 monProcessLookup
- 245572882 monProcessActivity
- 277572996 monProcessWorkerThread
- 309573110 monProcessNetIO
- 341573224 monProcessObject
- 373573338 monProcessWaits
- 405573452 monProcessStatement
- 437573566 monSysStatement
- 469573680 monProcessSQLText
- 501573794 monSysSQLText
- ^C
- C:\Documents and Settings\Administrator>
- C:\Documents and Settings\Administrator>bcp master..V_sysobjects_id_name out V_s
- ysobjects_id_name.txt --initstring " create view V_sysobjects_id_name as select
- id,name from sysobjects where type='U'" -c -Usa -P -Stest
- Starting copy...
- 87 rows copied.
- Clock Time (ms.): total = 16 Avg = 0 (5437.50 rows per sec.)
- C:\Documents and Settings\Administrator>more V_sysobjects_id_name.txt
- 1097051913 spt_values
- 1129052027 spt_monitor
- 1177052198 spt_limit_types
- 1088003876 syblicenseslog
- 1225052369 spt_ijdbc_table_types
- 1257052483 spt_ijdbc_mda
- 1305052654 spt_ijdbc_conversion
- 1337052768 ijdbc_function_escapes
- 1593053680 monTables
- 1625053794 monTableParameters
- 1657053908 monTableColumns
- 1689054022 monState
- 1721054136 monEngine
- 1753054250 monDataCache
- 1785054364 monProcedureCache
- 1817054478 monProcedureCacheMemoryUsage
- 1849054592 monProcedureCacheModuleUsage
- 1881054706 monOpenDatabases
- 1913054820 monSysWorkerThread
- 1945054934 monNetworkIO
- 1977055048 monErrorLog
- 2009055162 monLocks
- 2041055276 monDeadLock
- 2073055390 monWaitClassInfo
- 2105055504 monWaitEventInfo
- 2137055618 monCachedObject
- 21572084 monCachePool
- 53572198 monOpenObjectActivity
- 85572312 monIOQueue
- 117572426 monDeviceIO
- 149572540 monSysWaits
- 181572654 monProcess
- 213572768 monProcessLookup
- 245572882 monProcessActivity
- 277572996 monProcessWorkerThread
- 309573110 monProcessNetIO
- 341573224 monProcessObject
- 373573338 monProcessWaits
- 405573452 monProcessStatement
- 437573566 monSysStatement
- 469573680 monProcessSQLText
- 501573794 monSysSQLText
- ^C
- C:\Documents and Settings\Administrator>
在--initstring中的sql語句中創(chuàng)建了視圖V_sysobjects_id_name。 我們來看看bcp導(dǎo)出數(shù)據(jù)完成后，該視圖V_sysobjects_id_name還存在否？
- 1> use master
- 2> go
- 1> select name from sysobjects where name='V_sysobjects_id_name'
- 2> go
- name
- -------------------------------------------------------------------------------
- --------------------------------------------------------------------------------
- --------------------------------------------------------------------------------
- ----------------
- V_sysobjects_id_name
- (1 row affected)
- 1>
- 1> use master
- 2> go
- 1> select name from sysobjects where name='V_sysobjects_id_name'
- 2> go
- name
- -------------------------------------------------------------------------------
- --------------------------------------------------------------------------------
- --------------------------------------------------------------------------------
- ----------------
- V_sysobjects_id_name
- (1 row affected)
- 1>
視圖V_sysobjects_id_name仍然是存在的。此種方法和ase12.x中利用顯示創(chuàng)建的視圖導(dǎo)出數(shù)據(jù)的方法比較類似，只不過稍微簡便些罷了。
總結(jié)一下：
1. ase15.0及后續(xù)版本中實(shí)現(xiàn)了根據(jù)條件導(dǎo)出表的數(shù)據(jù)，這點(diǎn)很值得慶幸。
2. 此bcp導(dǎo)出的不是參數(shù)--initstring中sql語句的結(jié)果，而仍然是關(guān)鍵字bcp和out之間的表的數(shù)據(jù)。
3. 增加的參數(shù)--initstring實(shí)際上就是實(shí)現(xiàn)了能夠利用bcp工具向ASE引擎發(fā)送sql命令而已。
4. 參數(shù)--initstring中的sql語句在導(dǎo)出數(shù)據(jù)整個(gè)會話期間有效。所以，導(dǎo)出--initstring中創(chuàng)建的臨時(shí)表是可能的。
5. 歸根結(jié)底，bcp增加的這個(gè)新特性，僅僅是能夠通過bcp向ase引擎發(fā)送sql命令罷了。
作者簡介：andkylee,5年Sybase管理、維護(hù)經(jīng)驗(yàn)?，F(xiàn)任職于北京一IT運(yùn)維管理公司，Sybase DBA。熟悉Sybase的安裝、配置、調(diào)優(yōu)、監(jiān)控與排錯(cuò)，尤其精通Sybase數(shù)據(jù)庫的災(zāi)難恢復(fù)。自己深入研究Sybase數(shù)據(jù)庫的內(nèi)部物理存儲結(jié)構(gòu)，開發(fā)了能夠從Sybase數(shù)據(jù)庫設(shè)備文件中提取數(shù)據(jù)的工具；還編寫了一個(gè)能夠分析Sybase日志文件內(nèi)容，反解析出相應(yīng)SQL語句的程序?？梢蕴峁㏒ybase數(shù)據(jù)庫非常規(guī)恢復(fù)技術(shù)支持。Sybase非常規(guī)數(shù)據(jù)庫恢復(fù)包括：設(shè)備文件故障（如：頁面邏輯損壞，頁面物理損壞等，605、692錯(cuò)誤等等），誤操作（包括：誤更新update，誤刪除drop table，誤清空數(shù)據(jù)truncate table，等）等，本人都有相應(yīng)的處理辦法。
鏈接：http://blog.csdn.net/andkylee/archive/2010/07/11/5726694.aspx
分享題目：SybaseASE15中bcp實(shí)現(xiàn)有條件的導(dǎo)出表內(nèi)數(shù)據(jù)
網(wǎng)頁鏈接：http://m.5511xx.com/article/cdpdeeo.html
