Just a thought, Ian, but you might try this solution versus doing a QoQ with lists versus doing a nested loop to test for matches and see which one actually works better for you performance-wise.
If you've got 40k records, that's a lot of data to be shuttling back and forth! On Dec 14, 2007 1:14 PM, Ian Skinner <[EMAIL PROTECTED]> wrote: > Aha! That is the kind of outside the box thinking for which I was > looking. Presuming that the SQL DSN user has Create permission (50/50 > chance) this could work nicely. I'll pass it on to the co-worker > dealing with this issue. He is having trouble with this HOF account, > see CF-OT post to that affect. > > Thank You > Ian > > > Adrian Lynch wrote: > > You wouldn't have to "import the Access data into the SQL database", sorry, > > you started the with the quotes ;O). You can just select the data from > > Access, loop over it and generate the SQL in insert into a temp table. > > Something like... > > > > <cfquery name="theAccessQuery" datasource="..."> > > QUERY THE ACCESS DATA HERE > > </cfquery> > > > > <cfquery name="theSQLQuery" datasource="..."> > > > > CREATE #temp (COLUMNS GO HERE) > > > > <cfloop query="theAccessQuery"> > > INSERT INTO #temp (COLUMNS GO HERE) VALUES (VALUES GO HERE) > > </cfloop> > > > > SELECT FROM THE SQL TABLE HERE > > > > </cfquery> > > > > Why would you do this? Well, you now have all the Access data in SQL Server > > and have available all the functions and constructs. One that might be > > interesting is CHECKSUM and BINARY_CHECKSUM which I don't think is available > > in Access. > > > > Interesting post. > > > > Adrian > > > > -----Original Message----- > > From: Todd [mailto:[EMAIL PROTECTED] > > Sent: 14 December 2007 17:33 > > To: CF-Talk > > Subject: Re: Compare two tables > > > > > > "we can not import the Access data into the SQL database at this time at > > least." > > > > On Dec 14, 2007 12:11 PM, Dawson, Michael <[EMAIL PROTECTED]> wrote: > > > > > >> Since everyone else mentioned QoQ, another option would be creating a > >> temp table in SQL, loading the Access records, and then doing the query. > >> > >> M!ke > >> > >> -----Original Message----- > >> From: Ian Skinner [mailto:[EMAIL PROTECTED] > >> Sent: Friday, December 14, 2007 9:57 AM > >> To: CF-Talk > >> Subject: Compare two tables > >> > >> Anybody have a creative way to compare data in two tables and find all > >> records in table A not in table B and vice-a-versa. The data share a > >> common key, 'License Number'. > >> > >> The trick -- Table A is in an MS Access Database and Table B is in a MS > >> SQL server database. They are not aware of each other and we can not > >> import the Access data into the SQL database at this time at least. > >> > >> TIA > >> Ian > >> > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294832 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
