union with update

Mohd. Omar Daraz 21 Reputation points
2021-02-23T03:41:44.27+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,132 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-24T04:27:43.96+00:00

    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.


6 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-23T07:33:53.847+00:00

    Hi @Mohd. Omar Daraz ,

    Welcome to Microsoft Q&A!

    For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    I suppose that id will never updated in table1 and table2, you could refer below query and check whether it is working:

    update a  
    set a.city=b.city,a.name=b.name  
    from table3 a    
    inner join   
    (select * from table1  
    union  
    select * from table2  
    except  
    select * from table3) b on a.id=b.id  
    

    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.

    0 comments No comments

  2. Mohd. Omar Daraz 21 Reputation points
    2021-02-23T08:01:29.753+00:00

    Dear Melissa,

    Thanks for your reply for better understanding

    I have table 1 with column name- ID, name, City
    value (1,'a','newyork')
    value (2,'b','washington')
    value (3,'c','dallas')
    I have table 2 with column name- ID, name, City
    value (10,'x','sydney')
    value (20,'y','melbourne')
    value (30,'z','queensland')

    I did UNION ALL to both of these two table and put data to
    table 3 with column name- ID, name, City
    and value came
    value (1,'a','newyork')
    value (2,'b','washington')
    value (3,'c','dallas')
    value (10,'x','sydney')
    value (20,'y','melbourne')
    value (30,'z','queensland')

    Now I update data to table 1 with column name- ID, name, City
    value (555,'qqq','japan')
    value (666,'www','china')

    also I update data to table 2 with column name- ID, name, City
    value (888,'ppp','london')
    value (999,'ooo','france')

    Now i want to see in table 3

               value (1,'a','newyork')
               value (2,'b','washington')
               value (3,'c','dallas')
               value (10,'x','sydney')
               value (20,'y','melbourne')
               value (30,'z','queensland')
               value (555,'qqq','japan')
               value (666,'www','china')
               value (888,'ppp','london')
               value (999,'ooo','france')
    

    I hope this is not much more understandable and cleared

    regards,

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-02-23T08:38:33.46+00:00

    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.


  4. MelissaMa-MSFT 24,176 Reputation points
    2021-02-23T09:04:25.707+00:00

    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.

    0 comments No comments