Yes, MS Access uses "SELECT ... INTO to put the results into another table; I
can't remember for sure, but I think it actually creates the table, like an
Access 'Make Table' query, rather than just inserting into an existing one. And
to add to the confusion, MySQL uses SELECT ... INTO OUTFILE 'filename' FROM ...
to redirect the results of the select into a file on the filesystem.
This is one reason why using DBI or a similar API does not and probably cannot
lead to completely database-independent code. If you use any proprietary SQL
syntax, that SQL would have to be rewritten if you ever switch databases. The
biggest example that comes to mind is retrieving the ID of a row you just
inserted, that the database assigns. Yes, you could write extra code to do this
manually in a db-independent way, but then you're not using the full power of
your database; the same can be said of other proprietary SQL constructs. They
can often be avoided, but not without a little pain, and not without taking time
to figure out which constructs are proprietary and which are both standard and
commonly implemented. The extensions are all generally added in order to
distinguish the db vendor by better serving the user, so it seems to make sense
to use them when they help.
"Tim Scott" <[EMAIL PROTECTED]> on 05/23/2001 10:43:20
AM
To: [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: Re: AW: (not) Too stupid to bind a variable... :-)
It looks like there's some confusion over SQL syntax here. Is there a
database which allows you to effect an insert into a table by doing a
'select ... into ' ?
Oracle certainly doesn't and if Peter's using Oracle then what he suggests
should (and does) work fine. Having said that TIMTOWTDI :) ...
Looking at the plsql.t test with the DBD::Oracle distribution (lines 234...)
this shows how a cursor can be used to fetch data. This seems like the
neatest option to me and will possible prove the most optimal in various
situations. How portable it is to other databases I can't say.
Regards,
Tim
