that returns zero rows and then use the columnlist from that recordset
object as your column list:
<cfquery name="get">
SELECT *
FROM #myDynamicTable#
WHERE 1 = 0
</cfquery>
<cfquery name="getdata">
SELECT #get.columnlist#
FROM #myDynamicTable#
</cfquery>
<cfloop query="getdata">
<cfquery>
INSERT INTO #myDestinationTable#
(#get.columnlist#)
VALUES
(
<cfloop list="#get.columnlist#" index="col">
'#getdata[col][currentrow]#'</cfloop>
)
</cfquery>
</cfloop>
You'll probably need to add a little logic in that innermost CFLOOP for
dealing with different data types (possibly selecting one row in the 'get'
query to use for calculating the appropriate type), but that should be
reasonably close.
Cheers,
barneyb
> -----Original Message-----
> From: Richard Crawford [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 22, 2004 12:15 PM
> To: CF-Talk
> Subject: Re: Generic Table Copy from one DB to another
>
> Tony Weeg wrote:
>
> > uhh, Im not sure what ya mean. I guess you could have a random word
> > generator, and hope that it matches a table in your
> database or something
> > like that.
> >
> > :) seriously...tho', not sure what you mean, or where/why
> it would come into
> > play...im sure you have a valid need...just wondering.
> >
> > -----Original Message-----
> > From: Richard Crawford [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 22, 2004 2:42 PM
> > To: CF-Talk
> > Subject: Re: Generic Table Copy from one DB to another
> >
> > Tony Weeg wrote:
> >
> >
> >>with cf?
> >>
> >><cfquery name="get" datasource="dsn1">
> >> select * from thisTable
> >></cfquery>
> >>
> >><cfloop query="get">
> >>
> >> <cfquery name="set" datasource="dsn2">
> >> insert into newTable
> >> (column1, column2, column3)
> >> values
> >> ('#get.value1#','#get.value2#','#get.value3#')
> >> </cfquery>
> >>
> >></cfloop>
> >>
> >>that's just one way...that I usually use!
> >
> >
> > Spiffy!
> >
> > Is there a way to do this without specifically listing all
> of the table
> > names?
>
> Gonzo gave me a pretty good start, actually.
>
> But the problem is this: I want to be able to specify a table at
> runtime, and not have to worry about its structure. In the
> script you
> gave, you specifically give the column names: column1,
> column2, etc. I
> need a script where I don't have to write out the column names.
>
> In other words, in my simple book database, I have two
> tables: a table
> for authors and a table for titles, both with different
> structures. I
> want to be able to run the same generic script on both tables without
> having to make changes to the script itself.
>
>
>
> --
> Richard S. Crawford
> Programmer III,
> UC Davis Extension Distance Learning Group
> (http://unexdlc.ucdavis.edu)
> (916)327-7793 / [EMAIL PROTECTED]
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
