How To Insert A New Column In SQLite

Author: erics, Posted March 25th, 2011 at 10:28:13am

A brief intro to altering table structures in SQLite…

Note To Self

Greetings Self,

In the future when dealing with moving data between MySQL and SQLite, remember it is MUCH easier to add columns at the END of a table in SQLite, rather than insert them somewhere in the middle.

That being said, read on…

Procedural Examples

For example, to add column “address2” at the END of the table:

ALTER TABLE customers ADD COLUMN "address2" varchar(30) DEFAULT (NULL);

To INSERT a column into a specific spot in a table, the table must be recreated from scratch in SQLite:

First, rename the original table to a temp name:
ALTER TABLE customers RENAME TO t1;

Then, re-create the table with the original name, including the new column:
CREATE TABLE "customers" (
"accountid" varchar(30) DEFAULT (NULL) ,
"name" varchar(30) DEFAULT (NULL) ,
"address1" varchar(50) DEFAULT (NULL) ,
"address2" varchar(50) DEFAULT (NULL) ,
"city" varchar(30) DEFAULT (NULL) ,
"state" varchar(100) DEFAULT (NULL) ,
"zip" varchar(10) DEFAULT (NULL) ,
"phone" varchar(25) DEFAULT (NULL) );

Next, copy the old data from the original table:
INSERT INTO customers (accountid, name, address1, city, state, zip, phone) SELECT accountid, name, address1, city, state, zip, phone` FROM t1;

Finally, delete the original table now that the new one is finished:
DROP TABLE t1;

Here is some good software for dealing with SQLite on a Mac: http://www.desertsandsoftware.com/?realmesa_home

And this is a link to the SQLite FAQ: http://www.sqlite.org/faq.html#q11

Categories: How-To's, Technology Tags: ALTER, ALTER TABLE, howto, iOS, mysql, SQLite, tips