The insert statement below should insert one row into table PP_VIEWER_SETTINGS. Does it do that? Is it what you called "does not work"? To insert several rows you need to write a huge join of dblookup to itself, so your insert statement should look like this:
insert into PP_VIEWER_SETTINGS (...) select a.ItemValue, b.ItemValue, c.ItemValue, ... from dblookup a, dblookup b, dblookup c, ... where a.Category = "KvsSettings" and a.ItemName = "Company" and b.Category = "KvsSettings" and b.ItemName = "DspNextPrevious" and c.Category = "KvsSettings" and c.ItemName = "EnableCarts" ... and a.? = b.? and a.? = c.? ... ; Question marks here is the field which value should identify what row particular ItemName should go to. Pavel On Thu, Jul 5, 2012 at 11:03 PM, Sam Carleton <scarle...@miltonstreet.com> wrote: > 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
