RE: [sqlite] joining a table to the end of another table
just append the second table onto the first: insert into table1(fld1,fld2) select fld1,fld2 from table2; --- Richard Boyd <[EMAIL PROTECTED]> wrote: > Many thanks for the replies. > > I knew it must be something simple!! > > Cheers, > R. > > -Original Message- > From: John LeSueur [mailto:[EMAIL PROTECTED] > Sent: 31 January 2005 21:27 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] joining a table to the end of another table > > Richard Boyd wrote: > > > Hi, > > > > Im inexperienced with databases so forgive me if this is a silly > > question. > > > > What I want to do is join to separate tables together into one > table. > > This is to enable me to synchronize databases held on remote > systems. > > I would use a master table which would have the dates of each > update > > and then I could see which databases need updated so that they all > the > > same. > > > > Every time I add data to the main database I put it in a new table, > > > with exactly the same structure as its predecessors. Then updating > is > > a simple matter of checking to see which tables I have on the > remote > > machine and then copying across the tables which are not there. My > > problem is Im unclear how to link multiple tables sequentially. > Ive > > looked into joins but they seem to be a way of performing > relational > > searches which is not what Im after. > > > > See example below if its not clear what Im looking to do: > > > > Table 0 Table1 > > > > 0 | A 5 | F > > > > 1 | B 6 | G > > > > 2 | C 7 | H > > > > 3 | D > > > > 4 | E > > > > Combined table > > > > 0 | A > > > > 1 | B > > > > 2 | C > > > > 3 | D > > > > 4 | E > > > > 5 | F > > > > 6 | G > > > > 7 | H > > > > Im sure theres a simple way to do it but Im not sure how. > > > > Thanks in advance > > > > Richard > > > > > > > >No virus found in this outgoing message. > >Checked by AVG Anti-Virus. > >Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: > 27/01/2005 > > > > > insert into combined_table > select * from table0 > union > select * from table1 > > John > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 > > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
RE: [sqlite] joining a table to the end of another table
> Many thanks for the replies. > > I knew it must be something simple!! > > Cheers, > R. > insert into combined_table > select * from table0 > union > select * from table1 Note that 'union' will eliminate duplicate rows. Use 'union all' instead if you don't want this. __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250
RE: [sqlite] joining a table to the end of another table
Many thanks for the replies. I knew it must be something simple!! Cheers, R. -Original Message- From: John LeSueur [mailto:[EMAIL PROTECTED] Sent: 31 January 2005 21:27 To: sqlite-users@sqlite.org Subject: Re: [sqlite] joining a table to the end of another table Richard Boyd wrote: > Hi, > > I’m inexperienced with databases so forgive me if this is a silly > question. > > What I want to do is join to separate tables together into one table. > This is to enable me to synchronize databases held on remote systems. > I would use a master table which would have the dates of each update > and then I could see which databases need updated so that they all the > same. > > Every time I add data to the main database I put it in a new table, > with exactly the same structure as its predecessors. Then updating is > a simple matter of checking to see which tables I have on the remote > machine and then copying across the tables which are not there. My > problem is I’m unclear how to link multiple tables sequentially. I’ve > looked into joins but they seem to be a way of performing relational > searches which is not what I’m after. > > See example below if it’s not clear what I’m looking to do: > > Table 0 Table1 > > 0 | A 5 | F > > 1 | B 6 | G > > 2 | C 7 | H > > 3 | D > > 4 | E > > Combined table > > 0 | A > > 1 | B > > 2 | C > > 3 | D > > 4 | E > > 5 | F > > 6 | G > > 7 | H > > I’m sure there’s a simple way to do it but I’m not sure how. > > Thanks in advance… > > Richard > > > >No virus found in this outgoing message. >Checked by AVG Anti-Virus. >Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 > > insert into combined_table select * from table0 union select * from table1 John -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005
Re: [sqlite] joining a table to the end of another table
At 9:05 PM + 1/31/05, Richard Boyd wrote: See example below if it's not clear what I'm looking to do: Table 0 Table1 0 | A 5 | F 1 | B 6 | G 2 | C 7 | H 3 | D 4 | E Combined table 0 | A 1 | B 2 | C 3 | D 4 | E 5 | F 6 | G 7 | H I'm sure there's a simple way to do it but I'm not sure how. Richard, try the UNION ALL operator, something like this: SELECT a, b FROM t0 UNION ALL SELECT a, b FROM t1 -- Darren Duncan
Re: [sqlite] joining a table to the end of another table
Richard Boyd wrote: Hi, I’m inexperienced with databases so forgive me if this is a silly question. What I want to do is join to separate tables together into one table. This is to enable me to synchronize databases held on remote systems. I would use a master table which would have the dates of each update and then I could see which databases need updated so that they all the same. Every time I add data to the main database I put it in a new table, with exactly the same structure as its predecessors. Then updating is a simple matter of checking to see which tables I have on the remote machine and then copying across the tables which are not there. My problem is I’m unclear how to link multiple tables sequentially. I’ve looked into joins but they seem to be a way of performing relational searches which is not what I’m after. See example below if it’s not clear what I’m looking to do: Table 0 Table1 0 | A 5 | F 1 | B 6 | G 2 | C 7 | H 3 | D 4 | E Combined table 0 | A 1 | B 2 | C 3 | D 4 | E 5 | F 6 | G 7 | H I’m sure there’s a simple way to do it but I’m not sure how. Thanks in advance… Richard No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 insert into combined_table select * from table0 union select * from table1 John
Re: [sqlite] joining a table to the end of another table
Other database engines provide methods of doing this, but I don't think sqlite does. I would use an insert statement that uses select to copy the content of one table into the other. insert into X(field1,field2) select field1,field2 from Y --- Richard Boyd <[EMAIL PROTECTED]> wrote: > Hi, > > Im inexperienced with databases so forgive me if this is a silly > question. > > What I want to do is join to separate tables together into one table. > This > is to enable me to synchronize databases held on remote systems. I > would use > a master table which would have the dates of each update and then I > could > see which databases need updated so that they all the same. > > Every time I add data to the main database I put it in a new table, > with > exactly the same structure as its predecessors. Then updating is a > simple > matter of checking to see which tables I have on the remote machine > and then > copying across the tables which are not there. My problem is Im > unclear how > to link multiple tables sequentially. Ive looked into joins but they > seem > to be a way of performing relational searches which is not what Im > after. > > See example below if its not clear what Im looking to do: > > Table 0 Table1 > 0 | A 5 | F > 1 | B 6 | G > 2 | C 7 | H > 3 | D > 4 | E > > Combined table > 0 | A > 1 | B > 2 | C > 3 | D > 4 | E > 5 | F > 6 | G > 7 | H > > Im sure theres a simple way to do it but Im not sure how. > > Thanks in advance > Richard > > > > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.1 - Release Date: 27/01/2005 > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com