Skip to main content
U
Ultimatorx
Known Participant
March 22, 2011
Question

Inserting from one database to another

  • March 22, 2011
  • 2 replies
  • 1069 views

I have two databases A contains some records which does not exist in B and B contains some records.which does not exist in A

The two databases has different ID records scheme one uses autonumber and the other uid

This is what I intend to achieve:

1. I want to query database A using firstname and lastname from database B to know if record exist

2. If record DOES NOT exists it should insert the records in database A

I'll be glad if anyone can help with this thanks

This topic has been closed for replies.

2 replies

A
Adam Cameron.
Inspiring
March 23, 2011

You don't actually ask a question in your post, btw.  but anyway...

Are the DBs different DBs on the same DB server?  You don't really say. Which DB platform(s) are they on?  You might be able to give the DBs access to one another @ DB level, in which case you can keep all this processing on the DB (probably where it belongs).

How many records (to nearest order of magnitude will do) are we talking here?

--

Adam

U
UltimatorxAuthor
Known Participant
March 23, 2011

>Are the DBs different DBs on the same DB server?  You don't really say. Which DB platform(s) are they on?

The DB platform is MySQL, the database contains about 50000 records, the DBs are on the same DB server.

Thanks

Owainnorth
Owainnorth
Inspiring
March 23, 2011

In which case there's no CF involved - I'm pretty sure in MySQL you can  just reference another database (as long as you have the privs, obviously).

You can simply access database1.tablename.column, and database2.tablename.column; write yourself a MySQL stored procedure and it'll complete in a fraction of the time CF could do it.

Owainnorth
Owainnorth
Inspiring
March 22, 2011

CF only allows you to query one database at a time, so you'd need to do a Query of Queries. Take this for example:

<!--- Get all the data from A --->

<cfquery name="TableA" datasource="A">

     SELECT forename, surname

     FROM TableA

</cfquery>

<!--- Get all the data from B --->

<cfquery  name="TableB" datasource="B">

     SELECT forename, surname

     FROM TableB

</cfquery>

<!--- Then do a query to get anything missing from A --->

<cfquery name="GetMissingFromA">

  SELECT forename, surname

  FROM  TableB

  MINUS

  SELECT forename, surname

  FROM A

</cfquery>

<!--- Loop over that query, inserting into A --->

<cfloop query="GetMissingFromA">

  <cfquery datasource="A>

    INSERT INTO A...

  </cfquery>

</cfloop>

Then perform the last two for table B.

That's a quick fix, someone might suggest something more efficient. Depends how much data you have in the tables really.

U
UltimatorxAuthor
Known Participant
March 22, 2011

Thanks, I tried it but it came up with this error

Error Executing Database Query.


Query Of Queries syntax error.
Encountered "MINUS.
The error occurred in C:\web\www\up\fillup.cfm: line 35
33 : <!--- Then do a query to get anything missing from A --->
34 : 
35 : <cfquery name="GetMissingFromA" dbtype="query">
36 : 
37 :   SELECT surname, othernames
SQL   SELECT surname, othernames FROM pcn MINUS SELECT surname, othernames FROM profile

it appears the MINUS in the syntax is the problem, I will appreciate your help with this

D
Dan_Bracuk
Inspiring
March 22, 2011

I was wondering about the minus keyword myself.  If it's not supported by Q of Q, use

where somefield not in (a valuelist from the other query)

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices