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
[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
