Creating a new table on one DSN from a Select Statement on another DSN

Thanks! I will look into that.

There is another way.

Run your query once and get all the results you need.

Loop through your query and start sending mail to the spool. When you reach your specified limit, pause for the same interval your mail spool is on.

Google "coldfusion sleep" if you don't know how to pause a template.

Based on that answer, I see no need to create any tables at all. That includes the table that ate Manhatten which you are contemplating.

Instead, select the sql you need from the db that holds it, run it, and do whatever you need to do with the results.

Thanks to both of you for the feedback on my question. The tables that I am trying to build are for sending emails using cfmail. I have different sql statements stored in a table that are associated with the different emails that are sent. The different emails pull different fields so I wanted to avoid having to always create a table that had every possible field in it and was hoping that I could use the column names as a way to get the correct fields built. Seeing that I would need to query the schema information to get the field types it probably just makes sense to include all of the fields in the table I create and then insert the records. I was just hoping to not have to make adjustments to the table fields each time new fields are added to the select sql.

what is the purpose of this, if i may ask?
is it a kind of live back-up you are trying to perform?

you are probably better of doing it on the mysql end instead on in cf...
if you are not on a shared host, of course...

more to the point of your question:
a <cfquery> can only connect to ONE dsn at a time - you can't query 2
datasources with one <cfquery> tag.

a SELECT query returns an alphabetical list of columns in
queryname.columnlist variable. but that is all the info it returns about
the columns - just their names, in uppercase.

to create a copy of the table in your other dsn, you will need to know a
lot more about the columns and their data in the original table:
datatypes, value lengths, default values, charset and collation, table
type, indexes and other constraints, etc etc etc.

the only sure way to get all that data is by querying various tables in
the information_schema database (a mysql system db which holds info
about all the databases on the same mysql server) for data about that
particular db and table... it is NOT a piece of cake.

so, my .02$ for you will be: if you have access directly to mysql
utilities on your server - use them to create a sql dump of the table,
then use that dump to create that table in your other dsn.
this, actually, can be done in cf, by invoking mysqldump.exe with proper
arguments via <cfexecute> tag. check out mysql reference manual for
detailed info on using mysqldump.exe command-line interface.

hope this helps...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

To create a table, you need a create table query. The manual for your rbdms should have an example of the syntax. Otherwise, google "your rdbms create table". You'll also have to know your datatypes.

Depending on your requirements and resources, there are probably better options available.