[sqlite] drop, create and copy a table
Dear Mr. Smith and Dr. Hipp> Finally, I have an application that works, using your suggestions. Something is not working as I want, but finally, it works. Thanks very mucho for your help. Regards, Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu -Mensaje original- De: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de H?ctor Fiandor Enviado el: jueves, 26 de noviembre de 2015 07:22 a. m. Para: 'SQLite mailing list' Asunto: Re: [sqlite] drop, create and copy a table Dear Mr. Smith and Dr. Hipp: Thanks very much for your answers. I have learned a lot from your messages. Reviewing old message I found one from Mr. Smith that confirm this routines, that I have found previously but I miss something, and need to do all this in three "phases", I need to close the application after 1st, start again in the 2nd, close again, a so for. As you say, it can be done easily in one "phase". I have to trial and learn. Thanks very much again. I will tell you about my progress. Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu -Mensaje original- De: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de R Smith Enviado el: jueves, 26 de noviembre de 2015 02:46 a. m. Para: sqlite-users at mailinglists.sqlite.org Asunto: Re: [sqlite] drop, create and copy a table On 2015/11/26 4:08 AM, H?ctor Fiandor wrote: > Dear Mr. Hipp and other members: > > I have a table where I have introduced a lot of information during 2015. > > In December 31, I have to select the records to be continue in 2016. > Previously, I have obtained a copy of the 2015 table for any consult. > > In 2016, the year start with a table "cleaned" of the old records, with the > records to be continued as the firsts, for these reasons, I thought to drop > the table and create a new one, "cleaned" of any history related to 2015, > with the Id starting in "1". > > Really, I don?t know that making the step (2) suggested by Mr. Hipp followed > by step (3) without dropping the table "clean" the history of the table and > start the Id with "1". > > Really, I have not used de BEGIN...COMMINT commands. > > I will test the routines as suggested by Mr. Hipp. > > Any suggestion will be received as a gift. Hi Hector, This above statement reads very different to what you have asked before. There is obvious a language difficulty between us so I will say the next bit as verbose as possible (please forgive me if it sounds overly convoluted). We now understand that you wish to create the new table and copy from the old table, but only SOME information, not all of it, because you want to remove very old data that is no longer needed and begin a new set when starting the year 2016. The best way to do that is as Dr. Hipp suggested by first renaming the current (old) table to something else, such as "temp_mytable", ALTER TABLE "mytable" RENAME TO "temp_mytable"; then create the new empty table (which will reset all the AUTO-INC values etc.). In this step be sure to recreate all Index and Trigger objects for the new table, CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol); then copy the values you want to keep from the old table with a select-insert, like this: INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... LastCol FROM temp_mytable WHERE DateStamp > '2013'; (Or however you want to filter out the unneeded records), then, after that is done, simply remove the old table: DROP TABLE "temp_mytable"; I understand from your original writing you were looking for a faster way to do it, or to do it with less steps. This is something that will not be done many times, just once, so there is no need to find a more efficient way. You can easily script the process above and just execute it using the command line utility or from inside your program. Hope that sheds a bit more light, Cheers. Ryan ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] drop, create and copy a table
On 2015/11/26 4:08 AM, H?ctor Fiandor wrote: > Dear Mr. Hipp and other members: > > I have a table where I have introduced a lot of information during 2015. > > In December 31, I have to select the records to be continue in 2016. > Previously, I have obtained a copy of the 2015 table for any consult. > > In 2016, the year start with a table "cleaned" of the old records, with the > records to be continued as the firsts, for these reasons, I thought to drop > the table and create a new one, "cleaned" of any history related to 2015, > with the Id starting in "1". > > Really, I don?t know that making the step (2) suggested by Mr. Hipp followed > by step (3) without dropping the table "clean" the history of the table and > start the Id with "1". > > Really, I have not used de BEGIN...COMMINT commands. > > I will test the routines as suggested by Mr. Hipp. > > Any suggestion will be received as a gift. Hi Hector, This above statement reads very different to what you have asked before. There is obvious a language difficulty between us so I will say the next bit as verbose as possible (please forgive me if it sounds overly convoluted). We now understand that you wish to create the new table and copy from the old table, but only SOME information, not all of it, because you want to remove very old data that is no longer needed and begin a new set when starting the year 2016. The best way to do that is as Dr. Hipp suggested by first renaming the current (old) table to something else, such as "temp_mytable", ALTER TABLE "mytable" RENAME TO "temp_mytable"; then create the new empty table (which will reset all the AUTO-INC values etc.). In this step be sure to recreate all Index and Trigger objects for the new table, CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol); then copy the values you want to keep from the old table with a select-insert, like this: INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... LastCol FROM temp_mytable WHERE DateStamp > '2013'; (Or however you want to filter out the unneeded records), then, after that is done, simply remove the old table: DROP TABLE "temp_mytable"; I understand from your original writing you were looking for a faster way to do it, or to do it with less steps. This is something that will not be done many times, just once, so there is no need to find a more efficient way. You can easily script the process above and just execute it using the command line utility or from inside your program. Hope that sheds a bit more light, Cheers. Ryan
[sqlite] drop, create and copy a table
Dear Mr. Smith and Dr. Hipp: Thanks very much for your answers. I have learned a lot from your messages. Reviewing old message I found one from Mr. Smith that confirm this routines, that I have found previously but I miss something, and need to do all this in three "phases", I need to close the application after 1st, start again in the 2nd, close again, a so for. As you say, it can be done easily in one "phase". I have to trial and learn. Thanks very much again. I will tell you about my progress. Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu -Mensaje original- De: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de R Smith Enviado el: jueves, 26 de noviembre de 2015 02:46 a. m. Para: sqlite-users at mailinglists.sqlite.org Asunto: Re: [sqlite] drop, create and copy a table On 2015/11/26 4:08 AM, H?ctor Fiandor wrote: > Dear Mr. Hipp and other members: > > I have a table where I have introduced a lot of information during 2015. > > In December 31, I have to select the records to be continue in 2016. > Previously, I have obtained a copy of the 2015 table for any consult. > > In 2016, the year start with a table "cleaned" of the old records, with the > records to be continued as the firsts, for these reasons, I thought to drop > the table and create a new one, "cleaned" of any history related to 2015, > with the Id starting in "1". > > Really, I don?t know that making the step (2) suggested by Mr. Hipp followed > by step (3) without dropping the table "clean" the history of the table and > start the Id with "1". > > Really, I have not used de BEGIN...COMMINT commands. > > I will test the routines as suggested by Mr. Hipp. > > Any suggestion will be received as a gift. Hi Hector, This above statement reads very different to what you have asked before. There is obvious a language difficulty between us so I will say the next bit as verbose as possible (please forgive me if it sounds overly convoluted). We now understand that you wish to create the new table and copy from the old table, but only SOME information, not all of it, because you want to remove very old data that is no longer needed and begin a new set when starting the year 2016. The best way to do that is as Dr. Hipp suggested by first renaming the current (old) table to something else, such as "temp_mytable", ALTER TABLE "mytable" RENAME TO "temp_mytable"; then create the new empty table (which will reset all the AUTO-INC values etc.). In this step be sure to recreate all Index and Trigger objects for the new table, CREATE TABLE (Col1 INT, Col2 TEXT, etc. ... LastCol); then copy the values you want to keep from the old table with a select-insert, like this: INSERT INTO mytable (Col1, Col2, ... LastCol) SELECT Col1, Col2, ... LastCol FROM temp_mytable WHERE DateStamp > '2013'; (Or however you want to filter out the unneeded records), then, after that is done, simply remove the old table: DROP TABLE "temp_mytable"; I understand from your original writing you were looking for a faster way to do it, or to do it with less steps. This is something that will not be done many times, just once, so there is no need to find a more efficient way. You can easily script the process above and just execute it using the command line utility or from inside your program. Hope that sheds a bit more light, Cheers. Ryan ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] drop, create and copy a table
Dear Mr. Hipp and other members: I have a table where I have introduced a lot of information during 2015. In December 31, I have to select the records to be continue in 2016. Previously, I have obtained a copy of the 2015 table for any consult. In 2016, the year start with a table "cleaned" of the old records, with the records to be continued as the firsts, for these reasons, I thought to drop the table and create a new one, "cleaned" of any history related to 2015, with the Id starting in "1". Really, I don?t know that making the step (2) suggested by Mr. Hipp followed by step (3) without dropping the table "clean" the history of the table and start the Id with "1". Really, I have not used de BEGIN...COMMINT commands. I will test the routines as suggested by Mr. Hipp. Any suggestion will be received as a gift. Thanks very much for your answers. Yours Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu -Mensaje original- De: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] En nombre de H?ctor Fiandor Enviado el: mi?rcoles, 25 de noviembre de 2015 03:43 p. m. Para: 'General Discussion of SQLite Database' Asunto: [sqlite] drop, create and copy a table Dear members: I have asked previously about this matter but nobody answer me. I have a table to be copied in another one with other name as a temp. Then I drop the first one. Then I create another one with same as the dropped table. Then I copy from the temp to the new created. I do all this but in three phases I may answer is It is possible to do this in one phase? Thanks in advance Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] drop, create and copy a table
What are you trying to do? Rename the table? You can use https://www.sqlite.org/lang_altertable.html for that. Please make your intentions clearer. On Wed, Nov 25, 2015 at 6:43 PM, H?ctor Fiandor wrote: > Dear members: > > > > I have asked previously about this matter but nobody answer me. > > I have a table to be copied in another one with other name as a temp. > > Then I drop the first one. > > Then I create another one with same as the dropped table. > > Then I copy from the temp to the new created. > > > > I do all this but in three phases I may answer is It is possible to do this > in one phase? > > > > Thanks in advance > > > > Ing. H?ctor Fiandor > > hfiandor at ceniai.inf.cu > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Bernardo Sulzbach
[sqlite] drop, create and copy a table
On 11/25/2015 3:43 PM, H?ctor Fiandor wrote: > I have asked previously about this matter but nobody answer me. > > I have a table to be copied in another one with other name as a temp. > > Then I drop the first one. > > Then I create another one with same as the dropped table. > > Then I copy from the temp to the new created. > > > > I do all this but in three phases I may answer is It is possible to do this > in one phase? I can do it in zero phases. As far as I can tell from your description, the sequence leaves the database in the exact same state it started from. So you may as well do nothing at all and achieve the same result. -- Igor Tandetnik
[sqlite] drop, create and copy a table
On 11/25/15, H?ctor Fiandor wrote: > Dear members: > > > > I have asked previously about this matter but nobody answer me. > > I have a table to be copied in another one with other name as a temp. > > Then I drop the first one. > > Then I create another one with same as the dropped table. > > Then I copy from the temp to the new created. > Why not instead: (1) BEGIN; (2) ALTER TABLE table_to_change RENAME TO temporary_name; (3) CREATE TABLE table_to_change(... new schema here); (4) INSERT INTO table_to_change SELECT ... FROM temporary_name; (5) DROP TABLE temporary_name; (6) COMMIT; The BEGIN...COMMIT makes everything appear to happen all at once. The use of ALTER TABLE in step (2) means that you only copy the data once instead of twice. -- D. Richard Hipp drh at sqlite.org
[sqlite] drop, create and copy a table
Dear members: I have asked previously about this matter but nobody answer me. I have a table to be copied in another one with other name as a temp. Then I drop the first one. Then I create another one with same as the dropped table. Then I copy from the temp to the new created. I do all this but in three phases I may answer is It is possible to do this in one phase? Thanks in advance Ing. H?ctor Fiandor hfiandor at ceniai.inf.cu
