How to keep the original data while upgrading the datasheet

First, the question

When the project is late, or when the project version is upgraded, it is sometimes necessary to update the original database design. For example, if you have a table, you may need to add fields, modify fields, and so on. In some cases, the Alter Table command cannot accomplish this task, such as replacing the primary key. The original design can only be replaced with a new design. But what if you let the newly designed table contain the original data?

Second, the solution

Actually very simple, in 4 steps:

1. Create a new table
2. Inserting the original table data into a new table
3. Delete the original table
4. Rename the new table to the original table name

Third, examples

Well-known open source project BlogEngine.NET is doing this! For example, the following is an upgrade to the Be_posts table that was part of the SQLite database upgrade script that was used when it was upgraded from version 2.0 to version 2.5.

1 --2 --Update be_posts3 --4 Create TABLEBe_postsnew (5 [Postrowid] INTEGER  not NULL PRIMARY KEYAutoIncrement,6 [BlogID] VARCHAR( $) not NULL,7 [PostID] VARCHAR( $) not NULL,8 [Title] VARCHAR(255) not NULL,9 [Description] TEXT  not NULL,Ten [postcontent] TEXT  not NULL, One [DateCreated] DATETIME  not NULL, A [datemodified] DATETIME  not NULL, - [Author] VARCHAR( -) not NULL, - [ispublished]BOOLEAN not NULL, the [iscommentenabled]BOOLEAN not NULL, - [raters] INTEGER  not NULL, - [Rating] REAL  not NULL, - [Slug] VARCHAR(255) not NULL, + [IsDeleted]BOOLEAN not NULL - ); +   A Insert  intobe_postsnew at (BlogID, PostID, Title, Description, Postcontent, DateCreated, - datemodified, Author, ispublished, iscommentenabled, raters, - Rating, Slug, IsDeleted) - Select '27604f05-86ad-47ef-9e05-950bb762570c', P.postid, P.title, P.description, P.postcontent, p.datecreated, - p.datemodified, P.author, p.ispublished, p.iscommentenabled, P.raters, - p.rating, P.slug, p.isdeleted in  frombe_posts p; -   to Drop TABLEbe_posts; + Alter TABLEBe_postsnew RENAME tobe_posts;

How to keep the original data while upgrading the datasheet