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
