Hi Set! While I appreciate the distinction and tend to apply it obnoxiously (remember: I am still the guy who almost ripped a new one to a bloke who suggested in this list to use numbers in the order by...), there are some cases where practicality - in my view - trumps ideology.
Backup tables for testing purposes squarely falls in this camp :) I do agree it's *incorrect* but it's very handy and practical, especially when the database in question is one that you do not know well and you're not sure what kind of constraints there may be which make a blind DDL copy risky. Such an option would entirely avoid the issue by only copying the structure with no constraints - removing the need to think and get distracted from the task at hand (which is why the most widely known usability book is titled "Don't make me think"). A On Thu, Dec 10, 2015 at 10:25 PM, setysvar [email protected] [firebird-support] <[email protected]> wrote: > > > Hi Andrea! > > Creating a table is DDL (data definition language), inserting into it is > DML (data manipulation language). I NEVER mix DDL and DML in the same > transaction even though it is possible (though I don't think you can use a > table before the transaction that created it is committed). > > One thing you can try for fun on a test database, is to use EXECUTE BLOCK, > check if the table exist (SELECT * FROM RDB$RELATIONS WHERE > RDB$RELATION_NAME = 'ANDREA_TABLE'), if not create it using EXECUTE > STATEMENT ... IN AUTONOMOUS TRANSACTION and after that use another EXECUTE > STATEMENT ... IN AUTONOMOUS TRANSACTION for the INSERT (the table will be > created in an autonomous transaction that started after your main > transaction, so it is probably not visible otherwise). I've no idea whether > or not this will work (I don't even know whether "autonomous transaction" > is possible from "execute block"), but I wouldn't be very surprised if this > was a possible way to corrupt your database - e.g. I've no clue what > happens if two transactions try to create the same table simultaneously. > > Why do you want to create the table on the fly? I'd rather create such > tables in advance. A few empty, unused tables doesn't have much impact on > most databases (though, of course, I don't know whether you're talking > about doing this type of create/insert for one or possibly one million > tables, and lots of tables could possibly have an effect on performance - > at least for tools that read all metadata at startup). > > Set > > > Den 10.12.2015 15:33, skrev Andrea Raimondi [email protected] > [firebird-support]: > > Hello! > > I need to update several records in a table. > Obviously, the idea of doing so without being able to compare to the > previous values does not fill my heart with joy :) > > I have found a guide saying that I can insert into a table by > using a SQL statement - and that's fine, I like that. > > Alas, the table must already exist, so I am wondering if > someone knows of a way to also create it in the same > statement. It's one of the few things (VERY FEW things) I > miss from Oracle. > > Please help :) > > Thanks! > > A > > > > > -- Mr. Andrea Raimondi Senior Software Analyst&Developer
