dbpaces

hi, i want to copy using sql a table from 1 dbspace to another dbspace . Is this operation possible with ids 11.50? exemple: insert into dbpace1.database1.table1 from select * fromdbspace2.database2.table2 thank's for your help

Hi, If the databases and tables are in the same INFORMIXSERVER instance, you should be able to use: insert into database2:table2 select * from database1:table1; If they are in different instances, the only way I know of is to migrate the data (for example with unload/load). Otherwise, unless I've misunderstood something in your question, I don't think the dbspace the database and table are in should matter. Regards, Stuart From: "JACQUES ALFONSEA" <jalfonsea@numericable.fr> To: ids@iiug.org Date: 25/11/2011 16:41 Subject: dbpaces [25477] Sent by: ids-bounces@iiug.org hi, i want to copy using sql a table from 1 dbspace to another dbspace . Is this operation possible with ids 11.50? exemple: insert into dbpace1.database1.table1 from select * fromdbspace2.database2.table2 thank's for your help ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Unless stated otherwise above: IBM United Kingdom Limited - Registered in England and Wales with number 741598. Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6 3AU

Hi Jacques, Just create the destination table in the dbspace of your choice; it could even be in the same dbspace. The only this is that the table name has to be unique within a database if it is the same database. Make sure your detination table has the necessary columns to receive the data from the source table. CREATE TABLE dest_table (....) IN dbspace2;-- or CREATE RAW TABLE (....) IN dbspace2; INSERT INTO dest_table SELECT * FROM source_dest; -- this is valid ifall of the columns are used, otherwise specify the columns needed or INSERT INTO database2:dest_table SELECT * FROM database1:source_table;-- if the database source is different from the destination database; or INSERT INTO database2@informixserver:dest_name SELECT * FROMdatabase1@informixserver:source_table; -- if the Informix instances are different Advice: DO NOT CREATE THE INDEXES ON THE DESTINATION BEFORE THE COPY. If your destination table is part of database that uses logging (transactions), either change the logging mode of the database temporarily before the copy if the numbers of rows is substantial or CREATE a destination table in RAW type, copy the rows, change the type of the destination table to STANDARD type and add the indexes plus contraints if you want to have any. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 25/11/11 17:40, JACQUES ALFONSEA a écrit : > hi, > i want to copy using sql a table from 1 dbspace to another dbspace . Is this > operation possible with ids 11.50? > > exemple: > insert into dbpace1.database1.table1 from select * from> dbspace2.database2.table2 > > thank's for your help > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

You can do it but not like that. You would create a new table in the new dbspace with different name, copy the does, drop the old table and rename the new one. Fortunately you do not even have to do that. Just look up: ALTER FRAGMENT FOR TABLE table1 INIT IN newdbspace; Art On Nov 25, 2011 11:40 AM, "JACQUES ALFONSEA" <jalfonsea@numericable.fr> wrote: > hi, > i want to copy using sql a table from 1 dbspace to another dbspace . Is > this > operation possible with ids 11.50? > > exemple: > insert into dbpace1.database1.table1 from select * from> dbspace2.database2.table2 > > thank's for your help > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba4d35fae6a04b294a536

Jacques, I forgot something. If you go from one database to another, the database have to be at the same of logging (either both are logged or boeth are not logged). In case the databases are logged, use a table in RAW type to be faster and log the rows inserted from one table to another. Hope taht this helps. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 25/11/11 17:40, JACQUES ALFONSEA a écrit : > hi, > i want to copy using sql a table from 1 dbspace to another dbspace . Is this > operation possible with ids 11.50? > > exemple: > insert into dbpace1.database1.table1 from select * from> dbspace2.database2.table2 > > thank's for your help > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >