0

I have the following people table:

People:

| id   | f_name  | l_name    | role  |
|:-----|:-------:|:---------:| -----:|
| 1    | John    | Nathan    | 1     |
| 2    | Brand   |  Ba       | 1     |
| 3    | Bob     |  Do       | 2     |
| 4    | Alice   |  Sia      | 1     |

And users table:

Users:

| id   | f_name  | l_name | role  |
|:-----|:-------:|:------:| -----:|
| 1    | John    | Tom    |   1   |

I would like that anyone in People table with role=1 will be in Users table, so that if they aren't in users table already I insert them to the table, and if they do exist I update their first and last name and role based on People table based on id column

desired result:

Users:

| id   | f_name  | l_name    | role  |
|:-----|:-------:|:------:   | -----:|
| 1    | John    | Nathan    |   1   |
| 2    | Brand   | Ba        |   1   |
| 3    | Bob     | Do        |   1   |

Thanks in advance!

Dale K
  • 25,246
  • 15
  • 42
  • 71
liron29
  • 72
  • 7
  • 1
    This is called a `MERGE` or "Upsert". You'll find a wealth of information on how to do this if you search. – Thom A Jul 26 '21 at 08:50
  • 1
    ["People will probably tell you to use `MERGE` ... I urge you not to".](https://stackoverflow.com/a/52780490/3094533) learn from Aaron Bertrand. – Zohar Peled Jul 26 '21 at 09:02
  • Why would you want to store two copies of the same data? Just make a view – Charlieface Jul 26 '21 at 10:19
  • @DaleK I ended up using `MERGE` only on specific condition source. thanks everyone for helping. – liron29 Jul 26 '21 at 11:30

1 Answers1

1

As suggested by @Larnu you can use MERGE in sql server:

Schema and insert statements

create table people( id int ,f_name  varchar(25), l_name varchar(25),role  int);
insert into people values( 1    ,'John', 'Nathan', 1);     
insert into people values( 2    ,'Brand','Ba'    , 1);     
insert into people values( 3    ,'Bob',  'Do'    , 2);     
insert into people values( 4    ,'Alice','Sia'   , 1);     


create table Users( id int,  f_name varchar(25), l_name varchar(25), role int);
insert into Users values( 1    ,'John' ,'Tom' ,1 );

Merge Query:

MERGE Users AS TARGET
USING people AS SOURCE 
ON (TARGET.id = SOURCE.id) 
--When records are matched, update the records 
WHEN MATCHED 
THEN UPDATE SET TARGET.f_name = SOURCE.f_name, TARGET.l_name = SOURCE.l_name 
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (id, f_name, l_name,role) VALUES (SOURCE.id, SOURCE.f_name, SOURCE.l_name, SOURCE.role);

After merge:

  select * from users;

Output:

id f_name l_name role
1 John Nathan 1
2 Brand Ba 1
3 Bob Do 2
4 Alice Sia 1

db<>fiddle here

If you want to merge peoples with role 1 only:

Schema and insert statements:

 create table people( id int ,f_name  varchar(25), l_name varchar(25),role  int);
 insert into people values( 1    ,'John', 'Nathan', 1);     
 insert into people values( 2    ,'Brand','Ba'    , 1);     
 insert into people values( 3    ,'Bob',  'Do'    , 2);     
 insert into people values( 4    ,'Alice','Sia'   , 1);     
 
 
 create table Users( id int,  f_name varchar(25), l_name varchar(25), role int);
 insert into Users values( 1    ,'John' ,'Tom' ,1 );

Merge query:

 MERGE Users AS TARGET
 USING (select * from people where role=1) AS SOURCE 
 ON (TARGET.id = SOURCE.id ) 
 --When records are matched, update the records 
 WHEN MATCHED 
 THEN UPDATE SET TARGET.f_name = SOURCE.f_name, TARGET.l_name = SOURCE.l_name , TARGET.role=SOURCE.role
 --When no records are matched, insert the incoming records from source table to target table
 WHEN NOT MATCHED BY TARGET 
 THEN INSERT (id, f_name, l_name,role) VALUES (SOURCE.id, SOURCE.f_name, SOURCE.l_name, SOURCE.role);

After MERGE:

 select * from users;

Output:

id f_name l_name role
1 John Nathan 1
2 Brand Ba 1
4 Alice Sia 1

db<>fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26