6
Answers

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

Photo of Jay Krishna Reddy

Jay Krishna Reddy
4y
780
1

Table 1:

  1. PersNum       Name    Surname  
  2.     2321    Lenora  Springer  
  3.     2320    Donya   Gugino  
  4.     3326    Leland  Wittmer  
  5.     4588    Elmer   Mcdill  
Table 2:
  1. PersNum       Name    Surname  
  2.     2321    Lenora  Farney  
  3.     2320    Donya   Willimas  
  4.     3326    Leland  Wittmer  
  5.     4588    Maya    Mcdill  
  6.     7785    Yolanda Southall  
  7.     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!

Answers (6)

2
Photo of Jay Krishna Reddy
14 53.3k 5.4m 4y
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
 
2
Photo of Sachin Singh
NA 55.8k 88.5k 4y
@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.
2
Photo of Jay Krishna Reddy
14 53.3k 5.4m 4y
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'.
2
Photo of Mageshwaran R
NA 10.8k 1.1m 4y
Hi Krishna,
please refer this link:
https://codingsight.com/different-ways-to-compare-sql-server-tables-schema-and-data/
2
Photo of Kiran Mohanty
478 2.8k 315.3k 4y
Here you go.
  1. /* add new records */  
  2. insert into table1 (PersNum, name , Surname)  
  3.      select t2.PersNum, t2.name , t2.Surname  
  4.      from table2 t2  
  5.      where not exists(  
  6.              select 1 from table1 t1   
  7.              where t1.PersNum = t2. PersNum  
  8.      );  
  9.   
  10.   
  11. /* update existing records */  
  12. UPDATE   
  13.      table1   
  14. SET   
  15.      name = table2.name, Surname = table2.Surname  
  16. FROM   
  17.      table2    
  18.      where table1.PersNum = table2.PersNum;  
  19.          
  20.   
  21. select * from table1  
2
Photo of Sachin Singh
NA 55.8k 88.5k 4y
you can try Merge

  1. MERGE dbo.SomeTable AS target         
  2. -- this is your SOURCE table where the data comes from   
  3. USING dbo.AnotherTable AS source      
  4. -- this is the CONDITION they have to "meet" on  
  5. ON (target.SomeColumn = source.AnotherColumn)    
  6.   
  7. -- if there's a match, so if that row already exists in the target table,  
  8. -- then just UPDATE whatever columns in the existing row you want to update  
  9. WHEN MATCHED THEN                             
  10.     UPDATE SET Name = source.Name,  
  11.                OtherCol = source.SomeCol  
  12.   
  13. -- if there's NO match, that is the row in the SOURCE does *NOT* exist in the TARGET yet,  
  14. -- then typically INSERT the new row with whichever columns you're interested in  
  15. WHEN NOT MATCHED THEN    
  16.     INSERT (Col1, Col2, ...., ColN)    
  17.     VALUES (source.Val1, source.Val2, ...., source.ValN); 
 
Next Recommended Forum