As this is a bulk insert sort of query you should be able to insert what are
effectively constants relative to all the rows you're inserting, for
example:
insert
into hmls_commercial
( mls_number,INSERTED_BY,INSERTED_ON )
select mls_number,'Rick',#createodbcdatetime(now())#
from hmls_commercial_temp
where mls_number
not in ( select substring_index(mls_number, '_', 1 )
as mls_num
from hmls_commercial )
Will put 'Rick' and the current date-time in every inserted row.
If you need to be more dynamic than this you'll have to select ID's with a
'not in' query like the above and then loop over them inserting each new row
individually.
hth
Richard
> -----Original Message-----
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: 05 June 2008 19:42
> To: CF-Talk
> Subject: RE: Anything wrong with this code?
>
> Ok, that's working (using a different table for the insert,
> 'hmls_commercial' rather than the 'properties' table for
> testing) with this code
>
> insert
> into hmls_commercial
> ( mls_number )
> select mls_number
> from hmls_commercial_temp
> where mls_number
> not in ( select substring_index(mls_number, '_', 1 )
> as mls_num
> from hmls_commercial )
>
> I had to use 'substring_index...' because the mls_number in
> the final table is tagged with _h or _s depending on which
> MLS the property data is from.
>
> The final issue with this query, however, is how do I insert
> values that are not in the table supplying the data.
>
> What I mean is, when I run the query, I need to insert extra
> data into the target table, such as:
>
> values ( <cfqueryparam cfsqltype='cf_sql_varchar' value='hmls'>,
> <cfqueryparam cfsqltype='cf_sql_varchar'
> value='#mls_number#_h'> )
>
> How would I write the query so I can specify other values to
> be inserted?
>
> I had this same problem using the 'dual' function in mysql...
> it checked for the duplicates, but I couldn't insert
> additional values because the column count didn't match. At
> least I couldn't figure out a way to do it.
>
> But is it possible to modify your query for the additional
> values, Jochem?
>
> Thanks,
>
> Rick
>
>
>
> > -----Original Message-----
> > From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, June 05, 2008 1:58 PM
> > To: CF-Talk
> > Subject: Re: Anything wrong with this code?
> >
> > Rick Faircloth wrote:
> > > (Simplified version... MySQL 5)
> > >
> > > <cfloop query='get_hmls_commercial_data'>
> > >
> > > <cfquery name='insert_data' datasource='xxxxx'
> >
> > Do get_hmls_commercial_data and insert_data query the same
> database?
> > In that case you should do:
> >
> > INSERT INTO properties (mls_number)
> > SELECT ...
> > FROM ...
> > WHERE mls_number NOT IN (
> > SELECT mls_number
> > FROM properties
> > )
> >
> > Jochem
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306948
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
