Re: Compare two tables
I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Ian Skinner wrote: 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:294801 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
just thinking out loud here...could you select them both into a CF query struct, then do a q of q on the two result sets to extract whatever data you needed? On Dec 14, 2007 9:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: 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:294806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
Suck the two tables down as WDDX files - work with them locally and use query of queries? On Dec 14, 2007 10:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: 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:294799 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
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:294792 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
I'd probably make two separate queries, one to each database and then do a query of queries to find the data you're looking for. Koen ~| 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:294800 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
QoQ? On Dec 14, 2007 10:57 AM, Ian Skinner [EMAIL PROTECTED] wrote: 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:294803 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Ben Doom wrote: I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. ~| 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:294812 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
If you can pull both result sets into ColdFusion through their respective data sources, you should be able to use a QofQ. A left outer join isn't supported in QofQ's, but there are work-arounds: http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer-join-in-qu ery-of-queries/ ~Brad -Original Message- From: Ben Doom [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 10:12 AM To: CF-Talk Subject: Re: Compare two tables I don't think you can do a subselect in a QofQ (someone correct me if I'm wrong) so I think the easiest thing would be to pull both in and then walk the arrays. Brute force and inelegant, but is this something you need to do once, or is this something that must be done often? --Ben Doom Ian Skinner wrote: 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:294811 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
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:294817 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
Are you saying you want to only look at the License Number column? If that's the case, query both tables and then find the missing ones with Q of Q or the following UDF. http://www.cflib.org/udf.cfm?ID=660 Adrian -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: 14 December 2007 15:57 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:294818 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
I had considered the list idea a couple of different ways. I forgot to mention the size of the tables involved. One is ~40,000 records and the other is ~46,000. Are these list ideas going to hold up with lists several tens of thousands of items long? Dominic Watson wrote: If you can get them both into cfquery objs, then try this (untested): (QoQ = Query of Queries as below) cfquery name=inAccessNotInSQLServer dbtype=query SELECT * FROM qry_access WHERE URN NOT IN (#ValueList(qry_sqlServer.URN)#) /cfquery cfquery name=inSQLServerNotInAccess dbtype=query SELECT * FROM qry_sqlServer WHERE URN NOT IN (#ValueList(qry_access.URN)#) /cfquery Dominic ~| 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:294826 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
That was considered but we do not have authority to upload the Access table into the SQL database, thus the list line of my original post. Dawson, Michael 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:294825 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
The list stuff should work on that many records -- performance might be suspect though. I'm not sure what exactly was meant by not having the authority to put the data in SQL server. It's more of a technicality, but what if you dynamically loaded the data into a temp table or table variable in a cfquery tag and then do your join to that? Overall it might perform better and you wouldn't actually be putting the data in SQL server :) ~Brad -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:02 PM To: CF-Talk Subject: Re: Compare two tables I had considered the list idea a couple of different ways. I forgot to mention the size of the tables involved. One is ~40,000 records and the other is ~46,000. Are these list ideas going to hold up with lists several tens of thousands of items long? ~| 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:294830 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
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:294829 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
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
RE: Compare two tables
Nope, QofQ does now allow for outer joins either with the left outer syntax, nor with the *= syntax. The work-around is usually to union two selects like in the link I posted earlier. ~Brad -Original Message- From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:03 PM To: CF-Talk Subject: Re: Compare two tables You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| 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:294833 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| 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:294827 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Compare two tables
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:294823 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
If you can get them both into cfquery objs, then try this (untested): (QoQ = Query of Queries as below) cfquery name=inAccessNotInSQLServer dbtype=query SELECT * FROM qry_access WHERE URN NOT IN (#ValueList(qry_sqlServer.URN)#) /cfquery cfquery name=inSQLServerNotInAccess dbtype=query SELECT * FROM qry_sqlServer WHERE URN NOT IN (#ValueList(qry_access.URN)#) /cfquery Dominic ~| 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:294815 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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:294819 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Compare two tables
Scratch that idea - I just re-read the quickdocs and QoQ doesn't support joins at all. (*grumble*) Just saw where you found a possible solution... yay! On Dec 14, 2007 1:03 PM, C. Hatton Humphrey [EMAIL PROTECTED] wrote: Yeah, this is a case where it would be nice if QofQ had more capability. This would be simple with the ability to do an outer join between the tables. Luckily it only needs to be done once, or at least only a few times until it is determined what is out of sync between the old version (Access) and the new version (SQL) and it is reconciled. You can't do sub-selects but I'm pretty sure you can do outer joins... SELECT A.ID, A.LicenseNumber, B.ID, B.LicenseNumber FROM qOne A LEFT OUTER JOIN qTwo B ON A.LicenseNumber = B.LicenseNumber WHERE B.ID IS NULL That *should* return all of the records in qOne that don't have a match in qTwo... work with it from there for the rest ;) Hatton ~| 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:294831 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Compare two tables
This is one of a couple of things which was much easier to do in primitive databases like dBase or Clipper. But I would use an equivalent technique here, just looping in both queries in the same time. (not tested) CFQUERY NAME=q1 DATASOURCE=DS1 SELECT field ORDER BY field /CFQUERY CFQUERY NAME=q2 DATASOURCE=DS2 SELECT field ORDER BY field /CFQUERY CFSET index1 = 1 CFSET index2 = 1 CFLOOP CONDITION=index1 LTE DS1.recordCount OR index2 LTE DS2.recordCount CFIF index2 GT DS2.recordCount OR q2.field[index2] GT q1.field[index1] !--- q1.field[index1] is a record in DS1 which is not in DS2: do whatever you have to do with it, then skip to next record in DS1 --- CFSET index1 = index1 + 1 CFELSEIF index1 GT DS1.recordCount OR q1.field[index1] GT q2.field[index2] !--- q2.field[index2] is a record in DS2 which is not in DS1: do whatever you have to do with it, then skip to next record in DS2 --- CFSET index2 = index2 + 1 CFELSE !--- both records are the same: skip to next in both DS --- CFSET index1 = index1 + 1 CFSET index2 = index2 + 1 /CFIF /CFLOOP -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| 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:294838 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
Pardon me for correcting Brad: QofQ does NOT allow for outer joins I'm glad I'm not the only one that mistypes one letter which changes the entire meaning of a sentence. :-D M!ke -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 12:57 PM To: CF-Talk Subject: RE: Compare two tables Nope, QofQ does now allow for outer joins either with the left outer syntax, nor with the *= syntax. The work-around is usually to union two selects like in the link I posted earlier. ~Brad ~| 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:294842 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
Lol. That does make a difference, doesn't it. :) ~Brad -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 2:07 PM To: CF-Talk Subject: RE: Compare two tables Pardon me for correcting Brad: QofQ does NOT allow for outer joins I'm glad I'm not the only one that mistypes one letter which changes the entire meaning of a sentence. :-D M!ke ~| 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:294844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
I'm not talking about an import. I'm talking about a temp table that will last the duration of the connection. M!ke -Original Message- From: Todd [mailto:[EMAIL PROTECTED] Sent: Friday, December 14, 2007 11:33 AM 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:294840 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Compare two tables
That is the kind of outside the box thinking for which I was looking You are welcome! ;-) Adrian, thanks for having my back and writing the sample code. M!ke 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 ~| 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:294841 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
