comparing two tables

[8ginga8]

Hi everyone,

I need to compare to tables. one being the master and the other being a local table.

the master can have hundreds of records and the local have only a few. I need to compare the local to the master and display the ones that are the same and also display the ones that do not exist for the purpose of updating and adding to the master table.

Example:
MASTERTABLE
1,2,3,4,5

LOCALTABLE
1,2,3,4,5,6

OUTPUT
update 1,2,3,4,5
insert 6

hope someone can help

Thanks

 

[FALCONSEYE]

for update
select * from localTable
where exists (select * from masterTable
where masterTabl.somePK = localTable.somePK)


for insert
select * from localTable
where not exists (select * from masterTable
where masterTabl.somePK = localTable.somePK)

hope it helps..

http://www.jetansolutions.com

 

[8ginga8]

can this same thing be done if the tables that I am trying to compare are in two seperate databases

 

[FALCONSEYE]

yes, then you will refer them by

select * from dBName.dbo.localTable
...


hope it helps

http://www.jetansolutions.com

 

[8ginga8]

what is dbo

 

[8ginga8]

here is what I have

database 1 = local_groups
database 2 = master_groups
primary key in both tables = gr_local_id

<cfquery datasource="local_groups" name="get_local">
SELECT gr_local_id
FROM [GROUP]
where exists (select gr_local_id from groups_master.group
where gr_local_id = group.gr_local_id)

</cfquery>

this is not working

 

[FALCONSEYE]

what's 'not working'? do you get an error message ?
you wrote your database names are master_groups and local_groups, and you are using something like
[Group] and groups_master.group (?) i don't understand why...

http://www.jetansolutions.com

 

[8ginga8]

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\CFusionMX7\db\slserver54\logging\groups_master.mdb'.

The error occurred in C:\CFusionMX7\

http://wwwroot\CFIDE\admin\upload_step2.cfm:

line 39

37 : <br />
38 : <table>
39 : <cfquery datasource="local_groups" name="get_local">
40 :
41 :

 

[8ginga8]

it is an access database aswell

 

[FALCONSEYE]

the error you are getting has nothing to do with the query. your dB does not exist.
'C:\CFusionMX7\db\slserver54\logging\groups_master.mdb'.
groups_master.mdb'

Please go to this folder, C:\CFusionMX7\db\slserver54\logging\ and create groups_master.mdb .

hope it helps

http://www.jetansolutions.com

 

[imstillatwork]

can this same thing be done if the tables that I am trying to compare are in two seperate databases"

Not if they are only accessable through two seperate DSNs


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

[cough] query of queries



r937.com | rudy.ca

 

[imstillatwork]

yes, but that's not the same thing now is it? just kidding. My answers are sometimes cut short since I am at my 'day job'



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!