Access - compare two tables and update or insert data in first table

Jay Krishna Reddy

Table 1:

PersNum Name Surname
2321 Lenora Springer
2320 Donya Gugino
3326 Leland Wittmer
4588 Elmer Mcdill

Table 2:

PersNum Name Surname
2321 Lenora Farney
2320 Donya Willimas
3326 Leland Wittmer
4588 Maya Mcdill
7785 Yolanda Southall
1477 Hailey Pinner

I need to find a way to check the personal number (field "PersNum"), and then if PersNum exists, update Name and Surname in Table1. If PersNum doesn't exist, insert new row in Table1.

Thanks in Advance!

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jay Krishna Reddy6y ago
That's fixed Thanks!

I have one more doubt instead of updating all the records again and again can we update only the records which data is changed against the column level
Sachin Singh6y ago
@Krishna Reddy , it means you are trying to insert UserId in the table that either do not exist in primary key table or you are simply not providing UserId column to the table which is foreign key and not null in nature.
Jay Krishna Reddy6y ago
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tRTUser_Infos_tRTUsers_user_ou_info_user_id". The conflict occurred in database "myddname", table "dbo.tRTUsers", column 'user_id'.
Mageshwaran R6y ago
Hi Krishna,
please refer this link:
https://codingsight.com/different-ways-to-compare-sql-server-tables-schema-and-data/
Kiran Mohanty6y ago
Here you go.
/* add new records */
insert into table1 (PersNum, name , Surname)
     select t2.PersNum, t2.name , t2.Surname
     from table2 t2
     where not exists(
             select 1 from table1 t1
             where t1.PersNum = t2. PersNum
     );


/* update existing records */
UPDATE
     table1
SET
     name = table2.name, Surname = table2.Surname
FROM
     table2
     where table1.PersNum = table2.PersNum;


select * from table1
Sachin Singh6y ago
you can try Merge
MERGE dbo.SomeTable AS target
-- this is your SOURCE table where the data comes from
USING dbo.AnotherTable AS source
-- this is the CONDITION they have to "meet" on
ON (target.SomeColumn = source.AnotherColumn)

-- if there's a match, so if that row already exists in the target table,
-- then just UPDATE whatever columns in the existing row you want to update
WHEN MATCHED THEN
    UPDATE SET Name = source.Name,
               OtherCol = source.SomeCol

-- if there's NO match, that is the row in the SOURCE does *NOT* exist in the TARGET yet,
-- then typically INSERT the new row with whichever columns you're interested in
WHEN NOT MATCHED THEN
    INSERT (Col1, Col2, ...., ColN)
    VALUES (source.Val1, source.Val2, ...., source.ValN);