Re: [sqlite] INSERTing from another table data
On 20 Mar 2019, at 00:35, Simon Davies wrote: > On Tue, 19 Mar 2019 at 15:07, Tim Streater wrote: >> >> My use case is a mixture of these. My need is to copy a row from a table in >> one db (db1) to a table with identical schema in another db (db2). The >> complication is that there is an id column, so the row needs to get a new id >> in db2. >> >> At the minute I open db1, and do these steps (absid is the id column): >> >> attach database ':memory:' as mem >> create table mem.messages as select * from main.messages where >> absid= >> update mem.messages set absid=null >> attach database 'db2' as dst >> insert into dst.messages select * from mem.messages >> >> which works nicely but has too many steps. I've not found a way to reduce >> the step count. > > absid is integer primary key, or a new id would not be generated in > the above scenario; so the following should work: > > insert into dst.messages( notabsid_1, notabsid2,...) select > notabsid_1, notabsid_2,... from main.messages; Yes, I thought about that at the time, but preferred what I have done, as this allows me to have just the one place in the app where the table schema is defined. I do this trick to move or copy a row in several places in the app, with slight variants, and explicitly spelling out all the columns would mean I'd have to be sure I'd updated that in a number of places, were the schema to change from time to time. Not ideal, but better from a maintenance PoV. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing from another table data
On Tue, 19 Mar 2019 at 15:07, Tim Streater wrote: > > My use case is a mixture of these. My need is to copy a row from a table in > one db (db1) to a table with identical schema in another db (db2). The > complication is that there is an id column, so the row needs to get a new id > in db2. > > At the minute I open db1, and do these steps (absid is the id column): > > attach database ':memory:' as mem > create table mem.messages as select * from main.messages where > absid= > update mem.messages set absid=null > attach database 'db2' as dst > insert into dst.messages select * from mem.messages > > which works nicely but has too many steps. I've not found a way to reduce the > step count. absid is integer primary key, or a new id would not be generated in the above scenario; so the following should work: insert into dst.messages( notabsid_1, notabsid2,...) select notabsid_1, notabsid_2,... from main.messages; > -- > Cheers -- Tim Regards, Simon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing from another table data
On 19 Mar 2019, at 13:46, R Smith wrote: > Three ways in SQL to create and fill a table with data from another: > > 1. CREATE ... AS > Example: > CREATE TABLE newTable AS SELECT a,b,c FROM oldTable; > 2. CREATE TABLE + INSERT > Example: > CREATE TABLE newTable(a INT, b REAL, c TEXT); > INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable; My use case is a mixture of these. My need is to copy a row from a table in one db (db1) to a table with identical schema in another db (db2). The complication is that there is an id column, so the row needs to get a new id in db2. At the minute I open db1, and do these steps (absid is the id column): attach database ':memory:' as mem create table mem.messages as select * from main.messages where absid= update mem.messages set absid=null attach database 'db2' as dst insert into dst.messages select * from mem.messages which works nicely but has too many steps. I've not found a way to reduce the step count. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing from another table data
Wow! Thanks. I did not know these choices. Now I do. ;-) From: sqlite-users on behalf of R Smith Sent: Tuesday, March 19, 2019 09:46 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] INSERTing from another table data I see the "ignore this" retraction, but I thought to mention the following any way, for future reference: Three ways in SQL to create and fill a table with data from another: 1. CREATE ... AS Example: CREATE TABLE newTable AS SELECT a,b,c FROM oldTable; (This method has the advantage of being fast and cheap in programming time, but takes away control over column affinities etc.) https://www.w3schools.com/sql/sql_create_table.asp 2. CREATE TABLE + INSERT Example: CREATE TABLE newTable(a INT, b REAL, c TEXT); INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable; (This method gives more control over the new table's schema, but does require 2 steps). https://www.w3schools.com/sql/sql_insert_into_select.asp 3. SELECT ... INTO Example: SELECT a,b,c FROM oldTable INTO newTable; (This SQL has much the same advantages and disadvantages as 1. above, except that SQLite specifically does not support this method [that I know of]) https://www.w3schools.com/sql/sql_select_into.asp On 2019/03/19 3:15 PM, Jose Isaias Cabrera wrote: > Greetings. > > I have this table, > > > create table a (a, b, c); > > insert into a values (1, 2, 3); > > insert into a values (2, 3, 4); > > insert into a values (3, 4, 5); > > insert into a values (4, 5, 6); > > insert into a values (5, 6, 7); > > insert into a values (6, 7, 8); > > and I also have this table, > > > create table b (a, b, c, d, e); > > I want to INSERT the data in table a, to b. I tried these, > > sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, > 'user1','2019-03-01 14:22:33' FROM a); > Error: near "SELECT": syntax error > > I then tried, > sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, > 'user1','2019-03-01 14:22:33' FROM a)); > Error: 1 values for 5 columns > > and I also tried, > > sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), > 'user1','2019-03-01 14:22:33')); > Error: near ")": syntax error > > I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but > I couldn't make sense of it. > > Any help would be greatly appreciated. > > thanks. > > josé > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing from another table data
I see the "ignore this" retraction, but I thought to mention the following any way, for future reference: Three ways in SQL to create and fill a table with data from another: 1. CREATE ... AS Example: CREATE TABLE newTable AS SELECT a,b,c FROM oldTable; (This method has the advantage of being fast and cheap in programming time, but takes away control over column affinities etc.) https://www.w3schools.com/sql/sql_create_table.asp 2. CREATE TABLE + INSERT Example: CREATE TABLE newTable(a INT, b REAL, c TEXT); INSERT INTO newTable(a.b.c) SELECT a,b,c FROM oldTable; (This method gives more control over the new table's schema, but does require 2 steps). https://www.w3schools.com/sql/sql_insert_into_select.asp 3. SELECT ... INTO Example: SELECT a,b,c FROM oldTable INTO newTable; (This SQL has much the same advantages and disadvantages as 1. above, except that SQLite specifically does not support this method [that I know of]) https://www.w3schools.com/sql/sql_select_into.asp On 2019/03/19 3:15 PM, Jose Isaias Cabrera wrote: Greetings. I have this table, create table a (a, b, c); insert into a values (1, 2, 3); insert into a values (2, 3, 4); insert into a values (3, 4, 5); insert into a values (4, 5, 6); insert into a values (5, 6, 7); insert into a values (6, 7, 8); and I also have this table, create table b (a, b, c, d, e); I want to INSERT the data in table a, to b. I tried these, sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 'user1','2019-03-01 14:22:33' FROM a); Error: near "SELECT": syntax error I then tried, sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 'user1','2019-03-01 14:22:33' FROM a)); Error: 1 values for 5 columns and I also tried, sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 'user1','2019-03-01 14:22:33')); Error: near ")": syntax error I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but I couldn't make sense of it. Any help would be greatly appreciated. thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing from another table data
Ignore this. Sorry. I should always count to 10 before sending things. Apologies. From: sqlite-users on behalf of Jose Isaias Cabrera Sent: Tuesday, March 19, 2019 09:15 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] INSERTing from another table data Greetings. I have this table, create table a (a, b, c); insert into a values (1, 2, 3); insert into a values (2, 3, 4); insert into a values (3, 4, 5); insert into a values (4, 5, 6); insert into a values (5, 6, 7); insert into a values (6, 7, 8); and I also have this table, create table b (a, b, c, d, e); I want to INSERT the data in table a, to b. I tried these, sqlite> insert into b (a, b, c, d, e) values (SELECT a, b, c, 'user1','2019-03-01 14:22:33' FROM a); Error: near "SELECT": syntax error I then tried, sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c, 'user1','2019-03-01 14:22:33' FROM a)); Error: 1 values for 5 columns and I also tried, sqlite> insert into b (a, b, c, d, e) values ((SELECT a, b, c FROM a), 'user1','2019-03-01 14:22:33')); Error: near ")": syntax error I tried looking at the INSERT help, https://sqlite.org/lang_insert.html, but I couldn't make sense of it. Any help would be greatly appreciated. thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/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 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
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...
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
To: General Discussion of SQLite Database
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 dbloo
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...
> 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 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 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 >> 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
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 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 > 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 = "EnableFavorit
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 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, >
