Complicated Insert

[xSeanx]

I have several rows of data in a table which i want to insert into only 1 row
in another table.
Is it possible to do an insert query from a select query?
Alternatively how can i create a list variable from a query?
eg query:
SELECT Name FROM Names WHERE Country = '#Form.Country#'

So the list will look like: name1, name2, etc...

[The ScareCrow]

To do this totally in a single query would depend on what db your using.

But, you could do
<cfquery name="Q1">
SELECT Name FROM Names WHERE Country = '#Form.Country#'
</cfquery>
<cfquery name="Q2">
Insert Into myTable(
<cfloop from="1" to="#Q1.RecordCount#" index="idx">Name#idx#</cfloop>
Values(
<cfloop from="1" to="#Q1.RecordCount#" index="idx">
'#Q1.Name[idx]#'
<cfif idx NEQ Q1.RecordCount>
,
</cfif>
</cfloop>

Ken

[OldCFer]

I hope you're not planning on storing lists like that in the database. It is not good, usually.

[Scott*e]

Hi,

You can write queries like:

insert into tablename
(
..
)
select
....
from
someothertablename

just make sure the same number of colums and data types match.

You can use
QuotedValueList(queryname.fieldname) to create a quoted comma delimited list