Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
EXPORT query? |
Wed, Apr 22 2015 5:30 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
Is there a simple way to export a query result set in the same way as we can export a table? I have a solution that involves creating a temporary table with fields that match the query and :- EITHER reprocessing the query 'INSERT INTO temp ... SELECT ,,.... OR reading through the query inserting records into the temp table .... then using EXPORT TABLE to make the export file. Quite a lot of code involved. Am I missing something or is this something worth suggesting to Tim? I imagine it would be a doddle (as I have much faith in Tim) for Elevate to include an extension like:- INSERT INTO FILE "ArchivedOrders.CSV" IN STORE MyStore FORMAT DELIMITED SELECT * FROM Orders WHERE OrderDate BETWEEN DATE '2006-01-01' AND DATE '2006-12-31' Perhaps this is already available, but I can't see it in the manuals. Cheers Jeff |
Thu, Apr 23 2015 1:43 AM | Permanent Link |
Uli Becker | Jeff,
> I have a solution that involves creating a temporary table with fields > that match the query and :- You can create a (temporary) table from the query in one step, then export it, e.g.: CREATE TEMPORARY TABLE "ExportTable" AS SELECT * FROM MyTable WITH DATA Uli |
Thu, Apr 23 2015 3:02 AM | Permanent Link |
Peter | Jeff,
Did you mean export to CSV? This is one I use to make dozens of CSV files, one at a time. Note the peculiar delimiter; maybe you could use the 'standard' delimiter. There is also an Import method somewhere here - let me know if you need it. Regards, Peter. function TfrmMain.ExportOneTable(STblName, SFileName: string): boolean; const MAKE_CSV = 'EXPORT TABLE "%s" TO "%s.csv" IN STORE "CSVStore" '+ ' DELIMITER CHAR #8 QUOTE CHAR ''"'' DATE FORMAT ''yyyy-mm-dd'' '+ ' TIME FORMAT ''hh:mm:ss n'' AM LITERAL ''AM'' PM LITERAL ''PM'' '+ ' DECIMAL CHAR ''.'' BOOLEAN TRUE LITERAL ''True'' FALSE LITERAL ''False'' '+ ' INCLUDE HEADERS MAX ROWS -1'; begin Result := False; EDBDatabase1.Close; EDBDatabase1.Database := OPERDB; EDBDatabase1.DatabaseName := OPERDB; EDBQuery1.DatabaseName := OPERDB; EDBDatabase1.Open; try EDBQuery1.SQL.Clear; EDBQuery1.SQL.Add(Format(MAKE_CSV, [STblName, SFileName])); EDBQuery1.ExecSQL; except raise; end; Result := True; end; |
Thu, Apr 23 2015 4:45 AM | Permanent Link |
Uli Becker | Peter,
his question was, how to export a query result, not how to export a table generally. Uli |
Thu, Apr 23 2015 5:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Jeff
Uli's approach will work nicely. If its one you want to do frequently then consider a view - they can also be exported. Roy Lambert |
Thu, Apr 23 2015 11:20 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 23/04/2015 5:43 p.m., Uli Becker wrote:
> You can create a (temporary) table from the query in one step, then > export it, e.g.: > Yes, thanks Uli. That is what Have done in some instances - I was just hoping for a cleaner way. Cheers Jeff |
Fri, Apr 24 2015 5:30 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 23/04/2015 9:57 p.m., Roy Lambert wrote:
> Jeff > > > Uli's approach will work nicely. If its one you want to do frequently then consider a view - they can also be exported. > > Roy Lambert > Thanks Roy |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |