Inserting from another table...

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Inserting from another table...

Sam Carleton-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

Pavel Ivanov-2
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
<[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

Sam Carleton-2
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 <[hidden email]> 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
> <[hidden email]> 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
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

Pavel Ivanov-2
> 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 <[hidden email]> 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 <[hidden email]> 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
>> <[hidden email]> 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
>> > [hidden email]
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

Keith Medcalf

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

David Bicking-2
In reply to this post by Sam Carleton-2
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 <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Inserting from another table...

Keith Medcalf
In reply to this post by Keith Medcalf

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: [hidden email] [mailto:sqlite-users-
> [hidden email]] 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users