union with update

Question

Dear concern,

I have two tables table 1 = id, name, city
table 2 = id, name, city

I already combined all data of table 1 and table 2 and put them in table 3 (id,name,city) using UNION ALL

insert into table3
select *
from
( select * from dbo.table1
union all
select * from dbo.table2)t

now on regular base my table 1 and table 2 data update
so how I can put only the updated data from table 1 and table 2 to table 3

your help is really appreciable

thanks

Answer 1

Hi @Mohd. Omar Daraz ,

According to your requirement, you could consider to create triggers which could insert new data from table1, table2 to table3 antomatically.

Please refer below examples:

--create triggers on both tables  
CREATE TRIGGER Table1Insert ON table1  
FOR INSERT  
AS  
 INSERT INTO table3  
 select * FROM inserted  
  
 CREATE TRIGGER Table2Insert ON table2  
FOR INSERT  
AS  
 INSERT INTO table3  
 select * FROM inserted  

--insert some new data in table1 and table2  
insert into Table1 values  
  (555,'qqq','japan'),  
  (666,'www','china')  
      
  insert into Table2 values  
 (888,'ppp','london'),  
 (999,'ooo','france')  

--query table3  
 select * from table3  

Output:

id	name	city  
1	a	newyork  
2	b	washington  
3	c	dallas  
10	x	sydney  
20	y	melbourne  
30	z	queensland  
666	www	china  
555	qqq	japan  
999	ooo	france  
888	ppp	london  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Dear @MelissaMa-MSFT ,

thanks for you reply. Its long time I again came back to reply you

the solution you have given first time. worked only on 'int' not for nvarchat data type
INSERT INTO Table3
SELECT * FROM Table1 WHERE Id NOT IN (SELECT Id FROM Table3);

but the solution you given by creating trigger its `working superb
--create triggers on both tables
CREATE TRIGGER Table1Insert ON table1
FOR INSERT
AS
INSERT INTO table3
select * FROM inserted

now my question is as every month 40 tables created automatically in my SQL data base and I put all those 40 table data or rows to one single table called bm by creating 40 trigger is there any damage might occur to my production server or database damage or any issue to user end who enter data or regular basis to all those 40 tables

please confirm me as i try this trigger to production server

regards,

Answer 3

I guess the column Id in those three tables is unique. If that is a case, I think there should be an index on that column in the tables. If the operation is only to insert data to Table 3 from both Table 1 and 2, you do not need a UNION ALL.

INSERT INTO Table3
SELECT * FROM  Table1 WHERE Id NOT IN (SELECT Id FROM Table3);

INSERT INTO Table3
SELECT * FROM  Table2 WHERE Id NOT IN (SELECT Id FROM Table3);

Answer 4

Hi @Mohd. Omar Daraz ,

EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

select * from table1
union
select * from table2
except
select * from table3

So above whole part is combine all data from table 1 and table 2 and minus all the data from table3 in order to find out all updated part only.

For example, suppose (select * from table1 union select * from table2) as A and (select * from table3) as B. Then above query is A minus B which are the only updated data.

It is not load full data from table 1 and table 2 to table3 as you mentioned.

Please also refer my complete statement from below:

drop table if exists Table1,Table2,Table3  
  
create table Table1  
(  
id int,  
name varchar(20),  
city varchar(20)  
)  
  
insert into Table1 values  
(1,'a','newyork'),  
 (2,'b','washington'),  
(3,'c','dallas')  
  
create table Table2  
(  
id int,  
name varchar(20),  
city varchar(20)  
)  
  
insert into Table2 values  
(10,'x','sydney'),  
(20,'y','melbourne'),  
(30,'z','queensland')  
  
create table Table3  
(  
id int,  
name varchar(20),  
city varchar(20)  
)  
  
insert into table3  
select *  
from  
( select * from dbo.table1  
union all  
select * from dbo.table2)t  
  
insert into Table1 values  
 (555,'qqq','japan'),  
 (666,'www','china')  
  
 insert into Table2 values  
(888,'ppp','london'),  
(999,'ooo','france')  
  
--Method 1  
insert into table3  
select * from (  
(select * from table1  
union  
select * from table2)  
except  
select * from table3)a  
  
--Method 2  
 insert into  table3  
 select * from (  
 (select * from table1  
 except  
 select * from table3)   
 union  
 (select * from table2  
 except  
 select * from table3 ))a  
  
 --Method 3  
  insert into  table3  
 select * from table1  
 union  
 select * from table2  
 except  
 select * from table3  
  
select * from table3  

Output:

id name city  
1 a newyork  
2 b washington  
3 c dallas  
10 x sydney  
20 y melbourne  
30 z queensland  
555 qqq japan  
666 www china  
888 ppp london  
999 ooo france  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @Mohd. Omar Daraz ,

Please refer below:

insert into  table3  
select * from table1  
union  
select * from table2  
except  
select * from table3  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.