Deleting from multiple tables

I'd like to purge a test database of all data so I can start from scratch without having to run in "create table" SQL and the stored procedures. Is it possible to easily delete all rows from all tables in a database? I was wondering if this may be possible to do this in a stored proc like so: <pseudocode> FOR EACH table in systables WHERE table not a system table. DELETE FROM table;END FOR </pseudocode> Does anyone know if this is possible in Informix 10? Many thanks Andrew

Andrew2006 wrote: > I'd like to purge a test database of all data so I can start from > scratch without having to run in "create table" SQL and the stored > procedures. > > Is it possible to easily delete all rows from all tables in a > database? > > I was wondering if this may be possible to do this in a stored proc > like so: > > <pseudocode> > FOR EACH table in systables WHERE table not a system table. > DELETE FROM table;> END FOR > </pseudocode> > > Does anyone know if this is possible in Informix 10? > > Many thanks > > Andrew Version? If 11.50 (or later Version 10) ... http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_1236.htm?resultof=%22%74%72%75%6e%63%61%74%65%22%20%22%74%72%75%6e%63%61%74%22%20 TRUNCATE (IDS) Use the TRUNCATE statement to quickly delete all rows from a local table and free the associated storage space. You can optionally reserve the space for the same table and its indexes. Only Dynamic Server supports this implementation of the TRUNCATE statement, which is an extension to the ANSI/ISO standard for SQL. Extended Parallel Server supports a different implementation of TRUNCATE, as the section TRUNCATE (IDS) describes. Syntax Read syntax diagramSkip visual syntax diagram .- TABLE-. >>-TRUNCATE -+--------+--+-------------+--+- table --+----------> '- 'owner' -.-' '- synonym ' .- DROP STORAGE -. >--+----------------+------------------------------------------>< '- REUSE STORAGE '

Andrew2006 wrote: > I'd like to purge a test database of all data so I can start from > scratch without having to run in "create table" SQL and the stored > procedures. > > Is it possible to easily delete all rows from all tables in a > database? > > I was wondering if this may be possible to do this in a stored proc > like so: > > <pseudocode> > FOR EACH table in systables WHERE table not a system table. > DELETE FROM table;> END FOR > </pseudocode> > > Does anyone know if this is possible in Informix 10? > > Many thanks > > Andrew I think that it would be much easier (and quicker) to do something more like... dbschema -ss -d XYZ > /tmp/XYZ.sqlecho "drop database XYZ" | dbaccess dbaccess sysmaster /tmp/XYZ M.P.

> From: mpruet1@verizon.net > Subject: Re: Deleting from multiple tables > Date: Wed, 11 Feb 2009 12:31:47 +0000 > To: informix-list@iiug.org > > Andrew2006 wrote: > > I'd like to purge a test database of all data so I can start from > > scratch without having to run in "create table" SQL and the stored > > procedures. > > > > Is it possible to easily delete all rows from all tables in a > > database? > > > > I was wondering if this may be possible to do this in a stored proc > > like so: > > > > <pseudocode> > > FOR EACH table in systables WHERE table not a system table. > > DELETE FROM table;> > END FOR > > </pseudocode> > > > > Does anyone know if this is possible in Informix 10? > > > > Many thanks > > > > Andrew > I think that it would be much easier (and quicker) to do something more > like... > > dbschema -ss -d XYZ > /tmp/XYZ.sql> echo "drop database XYZ" | dbaccess > dbaccess sysmaster /tmp/XYZ > > I think the point was that he wanted to truncate the data in his test tables in the database so that he doesn't have to recreate the database tables. The simplest solution would be to use a single stored procedure ... The outer stored procedure trunc_db() would query the systables to get a list of all of the tables in the database. Then for each user table, you truncate it. If you know the list of tables and you do not want to truncate all of the tables you can do it either two ways. 1) Hard code the list of tables, 2) Create your own meta data table consisting of the columns test_name, tab_name where you can name individual tests and then list the tables that you need to truncate for that specific test. You can then use this data to also set up the load list for a specific test. HTH -G But hey! What do I know? Its not like I have written hundreds or more Junits where we have to recreate mock map data to test our code. (Oh wait, I did. But that was Oracle. Same concept.) -G _________________________________________________________________ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009

Madison is right however a bit trigger happy: dbschema -ss -d XYZ /tmp/XYZ.sqlecho "drop database XYZ; create database XYZ" | dbaccess dbaccess XYZ /tmp/XYZ.sql Superboer On 11 feb, 13:31, Madison Pruet <mpru...@verizon.net> wrote: > Andrew2006 wrote: > > I'd like to purge a test database of all data so I can start from > > scratch without having to run in "create table" SQL and the stored > > procedures. > > > Is it possible to easily delete all rows from all tables in a > > database? > > > I was wondering if this may be possible to do this in a stored proc > > like so: > > > <pseudocode> > > FOR EACH table in systables WHERE table not a system table. > >     DELETE FROM table;> > END FOR > > </pseudocode> > > > Does anyone know if this is possible in Informix 10? > > > Many thanks > > > Andrew > > I think that it would be much easier (and quicker) to do something more > like... > > dbschema -ss -d XYZ > /tmp/XYZ.sql> echo "drop database XYZ" | dbaccess > dbaccess sysmaster /tmp/XYZ > > M.P.

superboer7@t-online.de wrote: > Madison is right however a bit trigger happy: The mind is not yet in gear... ;-) > > dbschema -ss -d XYZ /tmp/XYZ.sql> echo "drop database XYZ; create database XYZ" | dbaccess > dbaccess XYZ /tmp/XYZ.sql > > > > Superboer > > > On 11 feb, 13:31, Madison Pruet <mpru...@verizon.net> wrote: >> Andrew2006 wrote: >>> I'd like to purge a test database of all data so I can start from >>> scratch without having to run in "create table" SQL and the stored >>> procedures. >>> Is it possible to easily delete all rows from all tables in a >>> database? >>> I was wondering if this may be possible to do this in a stored proc >>> like so: >>> <pseudocode> >>> FOR EACH table in systables WHERE table not a system table. >>> DELETE FROM table;>>> END FOR >>> </pseudocode> >>> Does anyone know if this is possible in Informix 10? >>> Many thanks >>> Andrew >> I think that it would be much easier (and quicker) to do something more >> like... >> >> dbschema -ss -d XYZ > /tmp/XYZ.sql>> echo "drop database XYZ" | dbaccess >> dbaccess sysmaster /tmp/XYZ >> >> M.P. >

Ian Michael Gumby wrote: > > > > From: mpruet1@verizon.net > > Subject: Re: Deleting from multiple tables > > Date: Wed, 11 Feb 2009 12:31:47 +0000 > > To: informix-list@iiug.org > > > > Andrew2006 wrote: > > > I'd like to purge a test database of all data so I can start from > > > scratch without having to run in "create table" SQL and the stored > > > procedures. > > > > > > Is it possible to easily delete all rows from all tables in a > > > database? > > > > > > I was wondering if this may be possible to do this in a stored proc > > > like so: > > > > > > <pseudocode> > > > FOR EACH table in systables WHERE table not a system table. > > > DELETE FROM table;> > > END FOR > > > </pseudocode> > > > > > > Does anyone know if this is possible in Informix 10? > > > > > > Many thanks > > > > > > Andrew > > I think that it would be much easier (and quicker) to do something more > > like... > > > > dbschema -ss -d XYZ > /tmp/XYZ.sql> > echo "drop database XYZ" | dbaccess > > dbaccess sysmaster /tmp/XYZ > > > > > > I think the point was that he wanted to truncate the data in his test > tables in the database so that he doesn't have > to recreate the database tables. Explain the difference. In either case wouldn't you end up with the same thing? Also don't forget that in order to truncate a table, you must first drop all of the RI constraints. By using dbschema to rebuild the database you don't. > > The simplest solution would be to use a single stored procedure ... > > The outer stored procedure trunc_db() would query the systables to get > a list of all of the tables in the database. > Then for each user table, you truncate it. > > If you know the list of tables and you do not want to truncate all of > the tables you can do it either two ways. > > 1) Hard code the list of tables, > 2) Create your own meta data table consisting of the columns test_name, > tab_name where you can name individual tests > and then list the tables that you need to truncate for that specific test. > > You can then use this data to also set up the load list for a specific test. > > HTH > > -G > > But hey! What do I know? Its not like I have written hundreds or more > Junits where we have to recreate mock map data to test our code. > (Oh wait, I did. But that was Oracle. Same concept.) > > -G > > > ------------------------------------------------------------------------ > Windows Live': E-mail. Chat. Share. Get more ways to connect. Check it > out. > <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009>;

Thanks for all the suggestions! superbo...@t-online.de wrote: > Madison is right however a bit trigger happy: > > dbschema -ss -d XYZ /tmp/XYZ.sql> echo "drop database XYZ; create database XYZ" | dbaccess > dbaccess XYZ /tmp/XYZ.sql > > > > Superboer > > > On 11 feb, 13:31, Madison Pruet <mpru...@verizon.net> wrote: > > Andrew2006 wrote: > > > I'd like to purge a test database of all data so I can start from > > > scratch without having to run in "create table" SQL and the stored > > > procedures. > > > > > Is it possible to easily delete all rows from all tables in a > > > database? > > > > > I was wondering if this may be possible to do this in a stored proc > > > like so: > > > > > <pseudocode> > > > FOR EACH table in systables WHERE table not a system table. > > >     DELETE FROM table;> > > END FOR > > > </pseudocode> > > > > > Does anyone know if this is possible in Informix 10? > > > > > Many thanks > > > > > Andrew > > > > I think that it would be much easier (and quicker) to do something more > > like... > > > > dbschema -ss -d XYZ > /tmp/XYZ.sql> > echo "drop database XYZ" | dbaccess > > dbaccess sysmaster /tmp/XYZ > > > > M.P.