In ase15, BCP can implement conditional export of table data.

---------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.
:
Http://blog.csdn.net/andkylee

---



2010-07-11
11:39:25

---- Keyword: ase bcp -- initstring conditional data export

----------------------------------------------------------------------------

In versions earlier than ase15.0, the BCP utility can only export the data of the whole table or view. To use bcp to export data in a table with conditions, you can create a view based on the conditions, and then export the data in the view; or create a temporary table based on the conditions, and then export the data in the temporary table. However, you must create an object (view or temporary table) in the database before executing the BCP command, and then execute the BCP command to export the view or temporary table data. Obviously, it is not too convenient. Other database management systems, such as SQL Server, have implemented the function of queryout data based on conditions as early as sqlserver2000. Using Sybase ASE's third-party tools to export data based on conditions is also a way.

 

In ASE 15.0 and later versions, Sybase finally provided a way to export data in the table according to the conditions. Although the implementation is a little bit of trouble, it eventually has this function. Parameter: -- initstring is added to implement this function.

Note:

1. the SQL statement after the parameter -- initstring will be sent to the ASE engine before the data is exported;

2. the SQL statement after the parameter-initstring is processed as a session-level SQL statement;

3. Parameter -- the SQL statement after initstring is always valid throughout the BCP data export session;

4. the exported data is the data of the table between the keyword BCP and out, rather than the data of the select list in the parameter-initstring.

 

Here are some examples:

 

BCP exports the data of the table between the keyword BCP and out, rather than the content of the select list in the -- initstring.

 

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 <br/> T -- initstring "select ID, name, type from master .. sysobjects where type = 'U' "-C <br/>-USA-p-stest <br/> starting copy... <br/> 163 rows copied. <br/> clock time (Ms .): Total = 16 AVG = 0 (10187.50 rows per sec .) <br/> C:/Documents and Settings/Administrator> More sysobjects.txt <br/> sysobjects 1 1 1 s 0 97 1 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> sysindexes 2 1 s 0 97 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> syscolumns 3 1 s 0 97 0 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> systypes 4 1 s 0 97 1 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> syslogs 8 1 s 0 1 0 0 73728 Dec 2 2 <br/> 009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 0 <br/> 0 0 0 0 <br/> syspartitions 28 1 s 0 97 2 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> sysgams 14 1 s 0 1 0 0 73728 Dec 2 2 <br/> 009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 0 <br/> 0 0 0 0 <br/> systabstats 23 1 s 0 97 1 0 229888 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> sysusages 31 1 s 0 97 2 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> sysdatabases 30 1 s 0 97 2 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> sysdevices 35 1 s 0 97 1 0 229376 <br/> Dec 2 2009 6: 58: 34: 590pm Dec 2 2009 6: 58: 34: 590pm 0 0 <br/> 0 0 0 0 0 1 <br/> 0000000000000000 <br/> ^ C <br/> C:/Documents and Settings/Administrator>

In the preceding example, select ID, name, type from Master .. although sysobjects where type = 'U' is executed, it is not reflected in the export result.

 

To export the ID and name columns of the User table in the sysobjects table, you can use the temporary table and view. However, this is different from the temporary table and view in versions earlier than ase15. Temporary tables or views created in versions earlier than ase15 are displayed, while temporary tables or views created in ase15 and later versions can be implicitly used to export data based on conditions.

 

Method 1: export data using implicit temporary table reason Conditions

 

Required: export the ID and name columns of the User table in the sysobjects table. The BCP command statement is as follows:

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

The effect is as follows:

C:/Documents and Settings/Administrator> BCP # temptbl out sysobjects_id_name.txt <br/> -- initstring "select ID, name into # temptbl from sysobjects where type = 'U' order <br/> by name "-C-USA-p-stest <br/> starting copy... <br/> 87 rows copied. <br/> clock time (Ms .): Total = 16 AVG = 0 (5437.50 rows per sec .) <br/> C: /Documents and Settings/Administrator> More sysobject_id_name.txt <br/> 233048835 hp_pageno_range <br/> 1065051799 ivcmplx <br/> 553049975 ivcon <br/> 601050146 ivcprop <br/> 425049519 ivonerow <br/> 793050830 ivparam <br/> 377049348 ivschm <br/> 457049633 ivscon <br/> 937051343 ivshare <br/> 889051172 ivsproc <br/> 649050317 ivsprop <br/> 1033051685 ivsrvr <br/> 505049804 ivssyn <br/> 985051514 ivstat <br/> 841051001 ivstr <br/> 697050488 ivsview <br/> 745050659 ivvcol <br/> 121048436 errorlog <br/> 1337052768 ijdbc_function_escapes <br/> 1093575903 bytes <br/> 265048949 lzflzf <br/> 21572084 moncachepool <br/> 2137055618 moncachedobject <br/> 533573908 moncachedprocedures <br/> 725574592 moncachedstatement <br/> 1753054250 mondatacache <br/> 2041055276 mondeadlock <br/> 117572426 mondeviceio <br/> 1721054136 monengine <br/> 1977055048 monerrorlog <br/> 85572312 monioqueue <br/> 661574364 monlicense <br/> 2009055162 monlocks <br/> 1945054934 monnetworkio <br/> 1881054706 monopendatabases <br/> 53572198 monopenobjectactivity <br/> 629574250 monopenpartitionactivity <br/> 757574706 monpcibridge <br/> 821574934 monpciengine <br/> 789574820 monpcislots <br/> 1785054364 monprocedurecache <br/> 1817054478 monprocedurecachememoryusage <br/> ^ C <br/> C: /Documents and Settings/Administrator>

 

Method 2: Use the implicitly created view to export data based on conditions

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

 

The effect is as follows:

C:/Documents and Settings/Administrator> BCP master .. v_sysobjects_id_name out v_s <br/> ysobjects_id_name.txt -- initstring "create view v_sysobjects_id_name as select <br/> ID, name from sysobjects where type = 'U' "-C-USA-p-stest <br/> starting copy... <br/> 87 rows copied. <br/> clock time (Ms .): Total = 16 AVG = 0 (5437.50 rows per sec .) <br/> C: /Documents and Settings/Administrator> more than <br/> 1097051913 spt_values <br/> 1129052027 spt_monitor <br/> 1177052198 spt_limit_types <br/> 1088003876 syblicenseslog <br/> 1225052369 spt_ijdbc_table_types <br/> 1257052483 spt_ijdbc_mda <br/> 1305052654 spt_ijdbc_conversion <br/> 1337052768 ijdbc_function_escapes <br/> 1593053680 montables <br/> 1625053794 montableparameters <br/> 1657053908 montablecolumns <br/> 1689054022 monstate <br/> 1721054136 monengine <br/> 1753054250 mondatacache <br/> 1785054364 monprocedurecache <br/> 1817054478 bytes <br/> 1849054592 monprocedurecachemoduleusage <br/> 1881054706 monopendatabases <br/> 1913054820 monsysworkerthread <br/> 1945054934 monnetworkio <br/> 1977055048 monerrorlog <br/> 2009055162 monlocks <br/> 2041055276 mondeadlock <br/> 2073055390 monwaitclassinfo <br/> 2105055504 monwaiteventinfo <br/> 2137055618 moncachedobject <br/> 21572084 moncachepool <br/> 53572198 monopenobjectactivity <br/> 85572312 monioqueue <br/> 117572426 mondeviceio <br/> 149572540 monsyswaits <br/> 181572654 monprocess <br/> 213572768 monprocesslookup <br/> 245572882 monprocessactivity <br/> 277572996 monprocessworkerthread <br/> 309573110 monprocessnetio <br/> 341573224 monprocessobject <br/> 373573338 monprocesswaits <br/> 405573452 monprocessstatement <br/> 437573566 monsysstatement <br/> 469573680 monprocesssqltext <br/> 501573794 monsyssqltext <br/> ^ C <br/> C: /Documents and Settings/Administrator>

 

In the SQL statement in -- initstring, the view v_sysobjects_id_name is created. Let's see if this view v_sysobjects_id_name still exists after BCP exports data?

1> use master <br/> 2> go <br/> 1> select name from sysobjects where name = 'v _ sysobjects_id_name '<br/> 2> go <br/> name <br/> alias <br/> ---------------- <br/> v_sysobjects_id_name <br/> (1 row affected) <br/> 1>

View v_sysobjects_id_name still exists. This method is similar to the method used to export data by displaying the created view in ase12.x, but it is a little easier.

 

Summary:

1. In ase15.0 and later versions, the table data is exported according to the conditions, which is very lucky.

2. This BCP is not the result of the SQL statement in the parameter -- initstring, but the data of the table between the keyword BCP and the out.

3. The added parameter, initstring, is actually the ability to use the BCP tool to send SQL commands to the ASE engine.

4. the SQL statement in the parameter -- initstring is valid during the entire data export session. Therefore, it is possible to export the temporary table created in -- initstring.

5. In the final analysis, this new feature added by BCP is only capable of sending SQL commands to the ASE engine through BCP.