update record if it exists, insert if doest not exists

[zyrag]

i have two tables within the same database. tblB is a temp table created at runtime, while tblA is a fix table. These 2 tables are not identical in structure.

I want to update records from tblA if the reference id exists in both tables and insert to tblA the records with reference id in tblB that doesn't exist in tblA.

Is there a straightforward way to this? From your experience, how would you achieve this?

thanks,

 

[KenReay]

Hi

Have an Update query, followed by an insert query

Something like

UPDATE tblA ... WHERE ReferenceId IN (SELECT ReferenceId FROM tblB);
INSERT INTO tblA ... SELECT ...FROM tblB;

and discard the error messages

logic - because ReferenceId is unique key the UPDATE stement will run only if record exists in tblA, similarly if ReferenceId already exists in tblA, then INSERT statement will fail

Cannot advise on how to discard the error messages, becuas eyou do not say how you plan to run these queries

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[zyrag]

forgot to mention... KenReay, i only used AutoNumber field as Primary Key for both tables and don't have any other Unique reference ID. I think your suggestion may not work.. Thanks anyway...

Right now, i do this as a remedy..

'Update existing records

rsDummy.MoveFirst
Do Until rsDummy.EOF
rsEmpBal.MoveFirst
Do Until rsEmpBal.EOF
If rsDummy!Emp_ID = rsEmpBal!Emp_ID Then
rsEmpBal!eHC = rsDummy!HC
rsEmpBal!eMC = rsDummy!MC
rsEmpBal!eODI = rsDummy!ODI
rsEmpBal!eOTH = rsDummy!OTH
rsEmpBal!ePA = rsDummy!PA
rsEmpBal!eSFL = rsDummy!SFL
rsEmpBal.Update
End If
rsEmpBal.MoveNext
Loop
rsDummy.MoveNext
Loop

'Insert records that don't exist

strSQL = "INSERT INTO tblEmpBal (Emp_ID,Branch_Code,MonthID,eHC,eMC,eODI,eOTH,ePA,eSFL) " _
& "SELECT Emp_ID,'" & strBranch & "'," & intMonth & ",SUM(HC),SUM(MC),SUM(ODI),SUM(OTH),SUM(PA),SUM(SFL) FROM " & tblName & " WHERE NOT EXISTS " _
& "(SELECT Emp_ID FROM tblEmpBal WHERE " & tblName & ".Emp_ID=tblEmpBal.Emp_ID " _
& "AND Branch_Code=' & strBranch & "' & " AND MonthID=" & intMonth & " AND YearID=2003) GROUP BY Emp_ID ORDER BY Emp_ID"

objConn.Execute strSQL, lngRecs, adCmdText

These worked but i'm still looking for a better way. If you have brilliant ideas, pls share it with me..

Thanks,

 

[jonathanmb]

Could you do something like this for your update?

'Update existing records

strSQL = "UPDATE tblA AS a, tblB AS b SET a.eHC = b.HC,  a.eMC = b.MC,  a.eODI = b.ODI,  a.eOTH = b.OTH, a.ePA = b.PA,  a.eSFL = b.SFL WHERE b.Emp_ID = a.Emp_ID"

--
Jonathan

 

[KenReay]

Hi

You mean an Autonumber in both tables?

In that case they will not match, so what do you use to match on, there must be somthing which identifies if it is an existing record or a new record ??

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jonathanmb]

From looking at what has been posted, it looks like both tables are related by the Emp_ID field.

If the Emp_ID field is the Primary Key and an AutoNumber type in both tables, that could most certainly cause problems when relating the two tables on that field. However, that will not prevent individual records from matching up between the two tables based on that field. The problem is whether or not each record in tblB (the temporary table) table matches up with the correct record in tblA (the permanent table). Depending on how the data is introduced and handled in both tables, it is possible for the AutoNumbers to match up correctly for records which exist in both tblA and tblB. However, I can't imagine why something would be done in this manner.

--
Jonathan

 

[zyrag]

Actually, these tables don't even need Primary key. The AutoNumber field in the fix table only serves as a unique identifier for each record. I don't relate these tables base on Emp_ID cause if i do, i will have a many-to-many relationship between them. The purpose of the temp table (tblB) is a temporary container containing the result of a filtered recordset. The records in these recordset will be compared to the fix table tblA, update existing records then insert those non-existing records. After then, tblB will be dropped.

In summary, this procedure will get the beginning balance for the month, add to it the running balance, update the ending balance, then insert those Emp_ID that are new or being transferred from one branch to another.

 

[KenReay]

Hi

I quote from your original post

"I want to update records from tblA if the reference id exists in both tables and insert to tblA the records with reference id in tblB that doesn't exist in tblA."

Which does not mention

"In summary, this procedure will get the beginning balance for the month, add to it the running balance, update the ending balance, then insert those Emp_ID that are new or being transferred from one branch to another."

If you tell us what you want to do, then maybe we can answer


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK