Sybase ASE15中bcp实现有条件的导出表内数据

richardtc 发表于 2010-10-18 13:55

Sybase ASE15.0之前的版本中利用bcp这个实用程序只能够导出整表或视图的数据。要想利用bcp有条件得导出表内数据，只能根据条件建立视图，然后再导出该视图的数据；或者根据条件建立临时表，再导出临时表的数据。但是，都必须在执行bcp命令之前到数据库内部去创建对象（视图或者临时表），然后再执行bcp命令导出视图或者临时表数据。显然，不是太方便。而其它的数据库管理系统，比如sqlserver早在sqlserver2000就实现了根据条件queryout数据的功能。利用sybase ASE的第三方工具按条件导出数据也是一种办法。

在Sybase ASE 15.0及以后版本中，sybase终于提供了按照条件导出表内数据的办法，虽然实现起来还稍微有一点点小麻烦，但是终究是有这个功能了。参数：--initstring 就是为实现这个功能而加的。
关于参数：--initstring的注意事项有：
1.参数--initstring之后的SQL语句会在数据导出之前被发送到ASE引擎；
2.参数--initstring之后的SQL语句被当做会话级别的SQL语句一样处理；
3.参数--initstring之后的SQL语句在整个bcp导出数据会话期间始终有效；
4.真正导出的数据是关键字bcp和out之间的表的数据，而不是参数--initstring中select列表的数据。
下面开始举几个例子:
bcp导出的是关键字bcp和out之间的表的数据，而不是--initstring中select列表的内容
bcp master..sysobjects out sysobjects.txt --initstring "select id,name,type from master..sysobjects where type='U' " -c -Usa -P -StestC:\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 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]sysindexes    2    1    S    0    97    0    0    229376 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]syscolumns    3    1    S    0    97    0    0    229376 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]systypes    4    1    S    0    97    1    0    229376 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]syslogs 8    1    S    0    1    0    0    73728 Dec2 2 0096:58:34:590PM    Dec2 20096:58:34:590PM    0    0    0 [*]0    0    0    0    0 syspartitions 28    1    S    0    97    2    0    229376 [*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 0    0    0    0    0    0                            1 [*]0000000000000000 sysgams 14    1    S    0    1    0    0    73728 Dec2 2 [*]0096:58:34:590PM    Dec2 20096:58:34:590PM    0    0    0 0    0    0    0    0 [*]systabstats 23    1    S    0    97    1    0    229888 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]sysusages    31    1    S    0    97    2    0    229376 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]sysdatabases 30    1    S    0    97    2    0    229376 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0 [*]0    0    0    0    0    0                            1 0000000000000000 [*]sysdevices    35    1    S    0    97    1    0    229376 Dec2 20096:58:34:590PM    Dec2 20096: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.txt --initstring "select id,name,type from master..sysobjects where type='U' " -c[*]-Usa -P -StestStarting copy...[*]163 rows copied.Clock Time (ms.): total = 16
Avg = 0 (10187.50 rows per sec.)[*]C:\Documents and Settings\Administrator>more sysobjects.txtsysobjects    1    1    S    0    97    1    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000sysindexes    2    1    S    0    97    0    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000syscolumns    3    1    S    0    97    0    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000systypes    4    1    S    0    97    1    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000syslogs 8    1    S    0    1    0    0    73728 Dec2 2[*]0096:58:34:590PM    Dec2 20096:58:34:590PM    0    0    00    0    0    0    0[*]syspartitions 28    1    S    0    97    2    0    229376Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0[*]0    0    0    0    0    0                            10000000000000000[*]sysgams 14    1    S    0    1    0    0    73728 Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    0    0[*]0    0    0    0    0systabstats 23    1    S    0    97    1    0    229888[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000sysusages    31    1    S    0    97    2    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000sysdatabases 30    1    S    0    97    2    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000sysdevices    35    1    S    0    97    1    0    229376[*]Dec2 20096:58:34:590PM    Dec2 20096:58:34:590PM    0    00    0    0    0    0    0                            1[*]0000000000000000^C[*]C:\Documents and Settings\Administrator>

上面的这个例子中，sql语句select id,name,type from master..sysobjects where type='U' 虽然被执行了，但是并没有被反映到导出的结果中。
想导出sysobjects表中用户表的id和name两列数据，可以利用临时表和视图来实现。但是这和ase15以前版本中的临时表和视图还是不一样的。ase15之前版本中临时表或者视图是显示创建的，而在ase15及后续版本中可以利用隐式创建的临时表或者视图来实现根据条件导出数据的要求。
方法一: 利用隐式创建的临时表来由条件导出数据
要求是：导出sysobjects表中用户表的id和name两列数据。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 -StestStarting 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.txt233048835    HP_PAGENO_RANGE[*]1065051799    IVCMPLX553049975    IVCON[*]601050146    IVCPROP425049519    IVONEROW[*]793050830    IVPARAM377049348    IVSCHM[*]457049633    IVSCON937051343    IVSHARE[*]889051172    IVSPROC649050317    IVSPROP[*]1033051685    IVSRVR505049804    IVSSYN[*]985051514    IVSTAT841051001    IVSTR[*]697050488    IVSVIEW745050659    IVVCOL[*]121048436    errorlog1337052768    ijdbc_function_escapes[*]1093575903    jdbc_function_escapes265048949    lzflzf[*]21572084    monCachePool2137055618    monCachedObject[*]533573908    monCachedProcedures725574592    monCachedStatement[*]1753054250    monDataCache2041055276    monDeadLock[*]117572426    monDeviceIO1721054136    monEngine[*]1977055048    monErrorLog85572312    monIOQueue[*]661574364    monLicense2009055162    monLocks[*]1945054934    monNetworkIO1881054706    monOpenDatabases[*]53572198    monOpenObjectActivity629574250    monOpenPartitionActivity[*]757574706    monPCIBridge821574934    monPCIEngine[*]789574820    monPCISlots1785054364    monProcedureCache[*]1817054478    monProcedureCacheMemoryUsage^C[*]C:\Documents and Settings\Administrator>

方法二: 利用隐式创建的视图来由条件导出数据
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 = 16Avg = 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_sysobjects_id_name.txt --initstring " create view V_sysobjects_id_name as select
[*]id,name
from sysobjects where type='U'" -c -Usa -P -StestStarting copy...[*]87 rows copied.Clock Time (ms.): total = 16Avg = 0 (5437.50 rows per sec.)[*]C:\Documents and Settings\Administrator>more V_sysobjects_id_name.txt1097051913    spt_values[*]1129052027    spt_monitor1177052198    spt_limit_types[*]1088003876    syblicenseslog1225052369    spt_ijdbc_table_types[*]1257052483    spt_ijdbc_mda1305052654    spt_ijdbc_conversion[*]1337052768    ijdbc_function_escapes1593053680    monTables[*]1625053794    monTableParameters1657053908    monTableColumns[*]1689054022    monState1721054136    monEngine[*]1753054250    monDataCache1785054364    monProcedureCache[*]1817054478    monProcedureCacheMemoryUsage1849054592    monProcedureCacheModuleUsage[*]1881054706    monOpenDatabases1913054820    monSysWorkerThread[*]1945054934    monNetworkIO1977055048    monErrorLog[*]2009055162    monLocks2041055276    monDeadLock[*]2073055390    monWaitClassInfo2105055504    monWaitEventInfo[*]2137055618    monCachedObject21572084    monCachePool[*]53572198    monOpenObjectActivity85572312    monIOQueue[*]117572426    monDeviceIO149572540    monSysWaits[*]181572654    monProcess213572768    monProcessLookup[*]245572882    monProcessActivity277572996    monProcessWorkerThread[*]309573110    monProcessNetIO341573224    monProcessObject[*]373573338    monProcessWaits405573452    monProcessStatement[*]437573566    monSysStatement469573680    monProcessSQLText[*]501573794    monSysSQLText^C[*]C:\Documents and Settings\Administrator>

在--initstring中的sql语句中创建了视图V_sysobjects_id_name。我们来看看bcp导出数据完成后，该视图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> go1> 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中利用显示创建的视图导出数据的方法比较类似，只不过稍微简便些罢了。
总结一下：
1.ase15.0及后续版本中实现了根据条件导出表的数据，这点很值得庆幸。
2.此bcp导出的不是参数--initstring中sql语句的结果，而仍然是关键字bcp和out之间的表的数据。
3.增加的参数--initstring实际上就是实现了能够利用bcp工具向ASE引擎发送sql命令而已。
4.参数--initstring中的sql语句在导出数据整个会话期间有效。所以，导出--initstring中创建的临时表是可能的。
5.归根结底，bcp增加的这个新特性，仅仅是能够通过bcp向ase引擎发送sql命令罢了。

andkylee 发表于 2010-10-18 15:50

转载的我的帖子： ASE15中bcp可以实现有条件的导出表内数据了http://blog.csdn.net/andkylee/archive/2010/07/12/5726694.aspx
http://blog.csdn.net/andkylee/archive/2010/07/12/5726694.aspx

也不注明出处。

wfcjz 发表于 2010-10-18 16:19

这个功能如果在最早的12.5的版本中有的话，估计原来的工作要省去很多事。

现在想想那个维护，采用BCP简直是场恶梦！

andkylee 发表于 2010-10-18 17:35

回复 3# wfcjz

用视图的话，麻烦是麻烦一些，但是对于大表在sybase中用bcp效率是最高的。

Eisen 发表于 2010-10-19 12:06

我用的那些default charset是iso_1的，有时候bcp一些有加密内容或含巨类型的表时，会发生bcp out得出来，bcp in不进去。
后来，我全都用replication server转数据了，效率似乎也不差。

页: [1]

Chinaunix's Archiver

Sybase ASE15中bcp实现有条件的导出表内数据