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

Question

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!

Jay Krishna Reddy · Answer

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 Singh · Answer

@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 Reddy · Answer

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 R · Answer

Hi Krishna, please refer this link: https://codingsight.com/different-ways-to-compare-sql-server-tables-schema-and-data/

Kiran Mohanty · Answer

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 Singh · Answer

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);