Have you tried to replace the "Insert into.... values(" with just "Select (" to
make sure the values are what you expect them to be. That might also point out
if any of them are null.
David
________________________________
From: Sam Carleton <scarle...@miltonstreet.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Thursday, July 5, 2012 11:03 PM
Subject: [sqlite] Inserting from another table...
I am working on converting my system table from one form to another. The
old form was one row per value with a category/key/value (DBLookup) , the
new form is a separate column for each value (PP_VIEWER_SETTINGS). I am
trying to create an insert statement to run when the new table is created,
but when I run it, it does not work nor do I get any errors in SQLite
manager:
/* The OLD one */
CREATE TABLE DBLookup (
Category VARCHAR(32) NOT NULL,
ItemName VARCHAR(128) NOT NULL,
ItemValue VARCHAR(3000) NOT NULL,
PRIMARY KEY(Category, ItemName))
/* The NEW one */
CREATE TABLE PP_VIEWER_SETTINGS
(
VIEWER_SETTINGS_ID INTEGER PRIMARY KEY
AUTOINCREMENT,
COMPANY_NAME VARCHAR( 260) NOT NULL,
DSPNEXTPREVIOUS SMALLINT NOT NULL,
ENABLE_CARTS SMALLINT NOT NULL,
ENABLE_DEBUGINFO SMALLINT NOT NULL,
ENABLE_FAVORITES SMALLINT NOT NULL,
ENABLE_RIGHTCLICK SMALLINT NOT NULL,
ENABLE_SLIDESHOW SMALLINT NOT NULL,
ENABLE_TIMEOUT SMALLINT NOT NULL,
EXIT_KVS SMALLINT NOT NULL,
EXIT_PASSWORD VARCHAR( 20) NOT NULL,
IS_CART_FAVORITES SMALLINT NOT NULL,
IS_LOGIN_REQUIRED SMALLINT NOT NULL,
IMAGE_SIZE INTEGER NOT NULL,
PHONE_NUM_FORMAT VARCHAR( 20) NOT NULL,
THEME_ID INTEGER NOT NULL,
THUMBNAIL_SIZE SMALLINT NOT NULL,
TICKER_MSG VARCHAR( 260) NOT NULL,
TO_AFTER SMALLINT NOT NULL,
TO_STARTS SMALLINT NOT NULL,
TO_TRANSITION_SECS SMALLINT NOT NULL,
SS_COUNT SMALLINT NOT NULL,
SS_DEFAULT_IS_IN_SLIDESHOW SMALLINT NOT NULL,
SS_DISPLAY_SECONDS DOUBLE PRECISION NOT NULL,
SS_ZOOM_FACTOR DOUBLE PRECISION NOT NULL,
USERLAN VARCHAR( 260) NOT NULL
);
/* The insert script */
insert into PP_VIEWER_SETTINGS
( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO,
ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW,
ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES,
IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID,
THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, TO_TRANSITION_SECS,
SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW,
SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN)
values (
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "Company"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "DspNextPrevious"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableCarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableDebugInfo"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableRightClick"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableSlideShow"),
1,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitKvs"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitPassword"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsCartFavorites"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsLoginRequired"),
900,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "PhoneNumberFormat"),
0,
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ThumbnailSize"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TickerMsg"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutAfter"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutStarts"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TransitionSeconds"),
0,
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DefaultIsInSlideShow"),
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "DisplaySeconds"),
(select ItemValue from dblookup where Category =
"SlideShowSettings" and ItemName = "ZoomFactor"),
(select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "USERLAN"));
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
