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
