ASE15.0之前的版本中利用bcp这个实用程序只能够导出整表或视图的数据。要想利用bcp有条件得导出表内数据，只能根据条件建立视图，然后再导出该视图的数据；或者根据条件建立临时表，再导出临时表的数据。但是，都必须在执行bcp命令之前到数据库内部去创建对象（视图或者临时表），然后再执行bcp命令导出视图或者临时表数据。显然，不是太方便。而其它的数据库管理系统，比如sqlserver早在sqlserver2000就实现了根据条件queryout数据的功能。利用sybase ASE的第三方工具按条件导出数据也是一种办法。
在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 -Stest view plaincopy to clipboardprint? C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> |
想导出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 |
效果如下：
| view plaincopy to clipboardprint? C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> |
方法二: 利用隐式创建的视图来由条件导出数据
| 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 |
| view plaincopy to clipboardprint? C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> C:Documents and SettingsAdministrator>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 SettingsAdministrator>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 SettingsAdministrator> |
| view plaincopy to clipboardprint? 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> |
总结一下：
1. ase15.0及后续版本中实现了根据条件导出表的数据，这点很值得庆幸。
2. 此bcp导出的不是参数–initstring中sql语句的结果，而仍然是关键字bcp和out之间的表的数据。
3. 增加的参数–initstring实际上就是实现了能够利用bcp工具向ASE引擎发送sql命令而已。
4. 参数–initstring中的sql语句在导出数据整个会话期间有效。所以，导出–initstring中创建的临时表是可能的。
5. 归根结底，bcp增加的这个新特性，仅仅是能够通过bcp向ase引擎发送sql命令罢了。
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
作者
相关推荐
-
SQL批量复制命令的六个陷阱
批量复制工具（BCP）是SQL Server主要的命令行工具之一，使用非常方便，它也是SQL Server导入导出海量数据的方式。
-
SQL Azure迁移工具：SSIS与BCP工具
使用SSIS连接Azure非常简单，只要进行正确的连接配置就可以。另外，你需要使用SSIS R2版本连接到SQL Azure。
-
自动输出SQL Server对象依赖列表到Excel文件
在本文中，作者介绍了自动输出SQL Server对象依赖列表到Excel文件的方法，并给出了具体的实现代码。
-
Sybase数据备份之BCP用法简介
BCP一般存放在所安装的ASE或者Open Client的BIN目录中。12版本以前的ASE，bcp存放目录为$SYBASE/bin，12版本以后存放目录为$SYBASE/OCS-12_x/bin。