Hi,

I am using VFP9 and SQL Server 2005.

I've got a database system set up such that there is a Central Database and Remote or Satellite Databases. The remote sites are not physically connected to the central site, so data entered there is either emailed or sent via some sort of disk back to the central site. The Central Database is used for storing all the data from all the remote sits for statistical analysis.

The data I need to send is basically from two tables:

ED Cards - edc_PK (identity column), edc_cardno (candidate key),

Card Answers - caa_PK (identity column), caa_edcFK (foreign key to ED Cards table),

The ED Card table has lots of foreign keys to various lookup tables.

We can have many thousands of records being sent (once a month all data gets sent and in one smallish island this data amounts to 20,000 odd records).

What I need to do is get the data into the central database, but I'll need to use new identity values for the "PK" fields, I'll also need to ensure that the "FK" fields correspond to the new identity values (the lookup tables should have the same code/description combination, but their primary keys may be different between the sites).

I have code which imports the ED Cards into a cursor and then scans through it getting the matching FK values for the lookup codes, then updating the Central database with this new value. However this is taking forever to process 20,000 records (upwards of 5 hours). If the edc_cardno value exists in the central database I need to update that record, rather than just appending the new record.

The code below may help to explain a bit more of the complexities involved as I don't feel I have explained the problem very well:

SELECT c_CardsReceived
m.loThermo1 = createobject("frmThermoBar", ;
            "Importing..." , ;
            m.lnReccount)
	
SCAN 
	m.loThermo1.lblMessage.Caption = "Importing " + c_CardsReceived.edc_cardno
	* add a new batch for each edcard
	IF NOT this.oBO_Batches.AddAction(@loMsgSvc)
		this.oBO_Batches.ShowMessage(@loMsgSvc)
		RETURN 
	ENDIF 
	m.edc_batfK = c_Batches.bat_PK
	UPDATE c_batches ;
		SET bat_batchno = PADL(X3GENPK("BATCHNO", "TI", .f.,.f.,.t.,.f.,.t.), 10, '0')

	SCATTER NAME m.loNewEDCard MEMO

	IF NOT this.oBO_EDCards.FetchData(@loMsgSvc, .F., "EDC_CardNo = '" + m.loNewEDCard.edc_cardno + "'")
		this.oBO_EDCards.ShowMessage(@loMsgSvc)
		RETURN 
	ENDIF 
        * edc_cardno not found in central database, so add it
	IF RECCOUNT('c_EDCards') = 0	&&_TALLY = 0
		IF NOT this.oBO_EDCards.AddAction(@loMsgSvc)
			this.oBO_EDCards.ShowMessage(@loMsgSvc)
			RETURN 
		ENDIF 
	ELSE 
            * need to update the edc_cardno record with latest data
	ENDIF 

	WITH m.loNewEDCard
		* convert the codes/names to use the PKs in this data set
		.edc_degfk = this.GetImportedLookupPK(.DataEntryGroup_code, .DataEntryGroup_name, 7)
		.edc_acarfk = this.GetImportedCarrierPK(.ArrivalCarrier_code, .ArrivalCarrier_name)
		.edc_dcarfk = this.GetImportedCarrierPK(.DepartureCarrier_code, .DepartureCarrier_name)
		.edc_brthfk = this.GetImportedLookupPK(.BirthCountry_code, .BirthCountry_name, 13)
		.edc_natnfk = this.GetImportedLookupPK(.Nationality_code, .Nationality_name, 10)
		.edc_issfk = this.GetImportedLookupPK(.IssueCountry_code, .IssueCountry_name, 1)
		.edc_embkfk = this.GetImportedLookupPK(.EmbarkCountry_code, .EmbarkCountry_name, 12)
		.edc_occpfk = this.GetImportedLookupPK(.Occupation_code, .Occupation_name, 5)
		.edc_ctryfk = this.GetImportedLookupPK(.ResidentCountry_code, .ResidentCountry_name, 1)
		.edc_accmfk = this.GetImportedAccommodationPK(.Accommodation_code, .Accommodation_name)
		.edc_rsonfk = this.GetImportedLookupPK(.Purpose_code, .Purpose_name, 6)
		.edc_portfk = this.GetImportedPortPK(.ArrivalPort_code, .ArrivalPort_name)
		.edc_dprtfk = this.GetImportedPortPK(.DeparturePort_code, .DeparturePort_name)
		.edc_typfk = this.GetImportedLookupPK(.VisitorType_code, .VisitorType_name, 9)
		.edc_gndrfk = this.GetImportedLookupPK(.Gender_code, .Gender_name, 17)
		.edc_cityfk = this.GetImportedCityPK(.City_code, .City_name)
		.edc_sttefk = this.GetImportedStatePK(.State_code, .State_name)
		.edc_pocfk = this.GetImportedPostCodePK(.Post_code)
		.edc_FirstVisitfk = this.GetImportedLookupPK(.FirstVisit_code, .FirstVisit_name, 25)
	ENDWITH 
					 
	SELECT c_Edcards
	m.lnFields = AFIELDS(laFields)
	SCATTER FIELDS EXCEPT edc_pk NAME m.loEDCard MEMO 
        * take the values from m.loNewEDCard and update the m.loEDCard with the new values
	FOR i = 1 TO m.lnFields
		IF UPPER(lafields[m.i,1]) # "EDC_PK" AND TYPE('m.loNewEDCard.' + lafields[m.i,1]) # "U"
			m.luValue = EVALUATE('m.loNewEDCard.' + lafields[m.i,1])
			IF NOT ISNULL(m.luValue) AND NOT EMPTY(m.luValue)
				m.loEDCard.&lafields[m.i,1]. = m.luValue
			ENDIF
		ENDIF 
	ENDFOR

	GATHER NAME m.loEDCard
	IF NOT this.oBO_EDCards.SaveAction(@loMsgSvc)
		this.oBO_EDCards.ShowMessage(@loMsgSvc)
		RETURN 
	ENDIF 
	
	IF NOT this.oBO_EDCards.FetchData(@loMsgSvc, .F., "EDC_CardNo = '" + m.loEDCard.edc_cardno + "'")
		this.oBO_EDCards.ShowMessage(@loMsgSvc)
		RETURN 
	ENDIF 
	m.edc_PK = c_edcards.edc_pk

	* coordinate the newly generated edc_pk with the survey answer details
	UPDATE c_SurveyAnswersReceived;
		SET adt_edcfk = m.edc_PK ;
		WHERE adt_edcfk = c_CardsReceived.edc_pk

	WITH m.loNewEDCard
		replace bat_carrfk WITH .edc_acarfk,;
			bat_flight WITH .edc_arrflight,;
			bat_portfk WITH .edc_portfk,;
			bat_month WITH .bat_month,;
			bat_year WITH .bat_year,;
			bat_actnumcards WITH 1,;
			bat_numcards WITH 1,;
			bat_usrfk WITH m.luUsr_PK,;
			bat_degfk WITH .edc_degfk,;
			bat_ulhfk WITH m.lnULH_PK;
		IN c_Batches
	ENDWITH
	
	IF NOT this.oBO_Batches.SaveAction(@loMsgSvc)
		this.oBO_Batches.ShowMessage(@loMsgSvc)
		RETURN 
	ENDIF 
m.loThermo1.UpdateStatusBar()

ENDSCAN

Hope this makes sense and someone out there might be able to suggest a better/faster way of doing this.