Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 19 total |
Best way to merge several tables into one? |
Fri, Sep 28 2007 11:29 AM | Permanent Link |
Dave Harrison | Is there a way to use SQL to take 10 tables with identical structure and
view them as one table? In MySQL there is something called a Merge table which logically joins the tables together (vertically-like an append) so it appears they are all one table without physically copying any data. It would be great if DBISAM or ElevateDb could achieve something like that. Failing that, is there a way in DB v4 to merge the data into one table using SQL? I'd like to do it in one or two operations if possible. TIA Dave |
Fri, Sep 28 2007 11:53 AM | Permanent Link |
Eryk Bottomley | Dave,
> Is there a way to use SQL to take 10 tables with identical structure and > view them as one table? In MySQL there is something called a Merge table > which logically joins the tables together (vertically-like an append) so > it appears they are all one table without physically copying any data. > It would be great if DBISAM or ElevateDb could achieve something like that. > > Failing that, is there a way in DB v4 to merge the data into one table > using SQL? I'd like to do it in one or two operations if possible. SELECT {whatever} UNION [ALL] ...etc. ...see help file for examples. Eryk |
Fri, Sep 28 2007 1:48 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Is there a way to use SQL to take 10 tables with identical structure and view them as one table? In MySQL there is something called a Merge table which logically joins the tables together (vertically-like an append) so it appears they are all one table without physically copying any data. It would be great if DBISAM or ElevateDb could achieve something like that. >> You can use UNION [ALL] to do so, but it will physically copy the rows from each table into one big result set. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Oct 1 2007 12:52 PM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << Is there a way to use SQL to take 10 tables with identical structure and > view them as one table? In MySQL there is something called a Merge table > which logically joins the tables together (vertically-like an append) so it > appears they are all one table without physically copying any data. It would > be great if DBISAM or ElevateDb could achieve something like that. >> > > You can use UNION [ALL] to do so, but it will physically copy the rows from > each table into one big result set. > This seems to work well if I extract just a small number of records from each table. But if I try and merge all tables into one using dbSys, then I get "DBISAM Engine Error # 10258 Cannot lock record in the table 'table_dest'". (Destination table) This is the same error I get when using dbSys to copy rows from a large table to a new table using "Insert into table2 select * from table1". It will get half way through after about 90 minutes and this error pops up. So it looks like it is impossible to move a lot of rows at a time with DBSys. (If it takes more than 90-120 minutes). Dave DBISAM 4.25 build 5 |
Mon, Oct 1 2007 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< This seems to work well if I extract just a small number of records from each table. But if I try and merge all tables into one using dbSys, then I get "DBISAM Engine Error # 10258 Cannot lock record in the table 'table_dest'". (Destination table) This is the same error I get when using dbSys to copy rows from a large table to a new table using "Insert into table2 select * from table1". It will get half way through after about 90 minutes and this error pops up. So it looks like it is impossible to move a lot of rows at a time with DBSys. (If it takes more than 90-120 minutes). >> How many rows are being copied ? Is the total size of the target table going to exceed 4GB ? If so, then you need to turn on large file support in the engine/DBSYS. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Oct 1 2007 4:24 PM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << This seems to work well if I extract just a small number of records from > each table. But if I try and merge all tables into one using dbSys, then I > get "DBISAM Engine Error # 10258 Cannot lock record in the table > 'table_dest'". (Destination table) > > This is the same error I get when using dbSys to copy rows from a large > table to a new table using "Insert into table2 select * from table1". It > will get half way through after about 90 minutes and this error pops up. > > So it looks like it is impossible to move a lot of rows at a time with > DBSys. (If it takes more than 90-120 minutes). >> > > How many rows are being copied ? Is the total size of the target table > going to exceed 4GB ? If so, then you need to turn on large file support in > the engine/DBSYS. > The data file is 1.3 gb, the index is 856 MB. This error happens every time I try and use DbSys to copy a large table. The problem might be it is trying to throw all the rows (15 million) into 1 transaction and eventually blows up. Dave |
Mon, Oct 1 2007 10:32 PM | Permanent Link |
"Donat Hebert \(Worldsoft\)" | That is certainly large. As a rule of thumb, you should consider a commit
interval for large inserts. ie Interval of 500 to 1000 records to take smaller bites than gulps > The data file is 1.3 gb, the index is 856 MB. This error happens every > time I try and use DbSys to copy a large table. The problem might be it is > trying to throw all the rows (15 million) into 1 transaction and > eventually blows up. > > Dave |
Tue, Oct 2 2007 5:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< The data file is 1.3 gb, the index is 856 MB. This error happens every time I try and use DbSys to copy a large table. The problem might be it is trying to throw all the rows (15 million) into 1 transaction and eventually blows up. >> Ah, yes, that's the issue. The OS might have an issue with that many row locks (15 million). You'll probably want to break up the INSERT into smaller transactional chunks in order to keep the number of row locks from escalating beyond a few thousand. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 3 2007 12:16 AM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << The data file is 1.3 gb, the index is 856 MB. This error happens every > time I try and use DbSys to copy a large table. The problem might be it is > trying to throw all the rows (15 million) into 1 transaction and eventually > blows up. >> > > Ah, yes, that's the issue. The OS might have an issue with that many row > locks (15 million). You'll probably want to break up the INSERT into > smaller transactional chunks in order to keep the number of row locks from > escalating beyond a few thousand. > Tim, Why is the OS doing row locks? I thought DBISAM would have locked the table until the query completed so there would be only 1 lock. How does Oracle or MS SQL handle it under Windows? Splitting the query into chunks within a loop is a bit of a pain for a one-time query. Has ElevateDb solved this problem? TIA Dave |
Wed, Oct 3 2007 3:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dave
Isn't it just a matter of setting the COMMIT interval? Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |