Re: [sqlite] Inserting from another table...
Pavel, The goal is to get them all into one row, correct. The query does not seem to do anything, the table is empty after I run the query. One question... In the PP_VIEWER_SETTINGS, I have columns of different types, but in DBLookup all the values are varchar's. Since sqlite doesn't actually deal with column types, I figured this would not be an issue. Might it be? Should I be casting them to the correct type first? Sam On Thu, Jul 5, 2012 at 11:24 PM, Pavel Ivanov paiva...@gmail.com wrote: 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_NAMEVARCHAR( 260) NOT NULL, DSPNEXTPREVIOUSSMALLINT 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_FORMATVARCHAR(20) NOT NULL, THEME_IDINTEGER 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
Re: [sqlite] Inserting from another table...
The query does not seem to do anything, the table is empty after I run the query. This can only mean that there was some error executing insert. It's written in a way that it just cannot leave empty table for any reason except error. You have all columns NOT NULL, so maybe dblookup doesn't have value for some column and thus the whole insert fails. One question... In the PP_VIEWER_SETTINGS, I have columns of different types, but in DBLookup all the values are varchar's. Since sqlite doesn't actually deal with column types, I figured this would not be an issue. Might it be? Should I be casting them to the correct type first? You don't have to. Your types declared in such way that SQLite will automatically try to convert all varchar values to integers and doubles where necessary. If SQLite won't be able to convert it will insert text value provided. But if you put explicit casting then SQLite will insert 0 if cast won't be successful. Pavel On Fri, Jul 6, 2012 at 2:45 PM, Sam Carleton scarle...@miltonstreet.com wrote: Pavel, The goal is to get them all into one row, correct. The query does not seem to do anything, the table is empty after I run the query. One question... In the PP_VIEWER_SETTINGS, I have columns of different types, but in DBLookup all the values are varchar's. Since sqlite doesn't actually deal with column types, I figured this would not be an issue. Might it be? Should I be casting them to the correct type first? Sam On Thu, Jul 5, 2012 at 11:24 PM, Pavel Ivanov paiva...@gmail.com wrote: 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_NAMEVARCHAR( 260) NOT NULL, DSPNEXTPREVIOUSSMALLINT 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_FORMATVARCHAR(20) NOT NULL, THEME_IDINTEGER 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,
Re: [sqlite] Inserting from another table...
Does saying what you want to do this way work better? 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) select (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); --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting from another table...
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
Re: [sqlite] Inserting from another table...
You should probably replace all the with ' as well. is for identifiers (column/table names, etc.). ' is for strings. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Keith Medcalf Sent: Friday, 06 July, 2012 13:29 To: General Discussion of SQLite Database Subject: Re: [sqlite] Inserting from another table... Does saying what you want to do this way work better? 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) select (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); --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ 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
Re: [sqlite] Inserting from another table... (resolved)
Folks, I would like to thank one and all, I think this was a group effort. I changed the double quotes to single, changed the value into a select and then just ran the select part and found one NULL filed because I had the wrong category. Fixed that and left it as a select and all is well! Thank you, one and all! On Fri, Jul 6, 2012 at 4:58 PM, Keith Medcalf kmedc...@dessus.com wrote: You should probably replace all the with ' as well. is for identifiers (column/table names, etc.). ' is for strings. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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_NAMEVARCHAR( 260) NOT NULL, DSPNEXTPREVIOUSSMALLINT 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_FORMATVARCHAR(20) NOT NULL, THEME_IDINTEGER 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
Re: [sqlite] Inserting from another table...
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_NAMEVARCHAR( 260) NOT NULL, DSPNEXTPREVIOUSSMALLINT 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_FORMATVARCHAR(20) NOT NULL, THEME_IDINTEGER 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),
