question

MohdOmarDaraz-1660 avatar image
0 Votes"
MohdOmarDaraz-1660 asked ·

union with update

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-generalsql-server-transact-sql
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MohdOmarDaraz-1660
The tag openspecs-sqlserver is dedicated to supporting issues related to open specifications. You can find open specifications at https://docs.microsoft.com/en-us/openspecs/

Your question is not related to open specifications. For a better chance of obtaining an answer, I am moving this question to sql-server-general tag.

Regards,
Obaid Farooqi - MSFT

0 Votes 0 ·

It's not clear for me what you want to update where?
Please post table design as DDL, some sample data as DML and the expected result.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @MohdOmarDaraz-1660,

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MohdOmarDaraz-1660,

Could you please try above method and let me know if any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @MohdOmarDaraz-1660,

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MohdOmarDaraz-1660 avatar image
0 Votes"
MohdOmarDaraz-1660 answered ·

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,


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @MohdOmarDaraz-1660,

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.

· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear Melissa,

with your reply every time full data from table 1 and table 2 will load to table 3

but in my previous post I stated I only want updated part from table 1 and table 2 and put them to table 3

this a huge database like 47,00,000 data so I cant load whole table 1 and table 2 and put data to table 3

regards,

0 Votes 0 ·
MelissaMa-msft avatar image MelissaMa-msft MohdOmarDaraz-1660 ·

Hi @MohdOmarDaraz-1660,

Above is the same as below which may be a little more understandable.

 insert into  table3
 select * from (
 (select * from table1
 except
 select * from table3) 
 union
 (select * from table2
 except
 select * from table3 ))a

Best regards
Melissa

0 Votes 0 ·

Dear Melissa,

really apricate your help

I have followed your each steps but when I was at following step I got error

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


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

not sure where is my mistake

Please help

Regards,

0 Votes 0 ·

I tried with *

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

but no luck

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

0 Votes 0 ·
MelissaMa-msft avatar image MelissaMa-msft MohdOmarDaraz-1660 ·

Hi @MohdOmarDaraz-1660,

I edited my previous answer and provided all related 3 methods using UNION and EXCEPT.

Please refer above updated query and choose any of the methods.

If you still face any error, please let me know.Thanks.

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @MohdOmarDaraz-1660,

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

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);
· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear Yuan,

thanks for your reply. and it works for me.

Only the updated data from table 1 and table 2 came into table 3

my question is as this is SQL server is running in production env so we have more than 47,00,000 data. and I have to take updated data from table 1, table 2, table 3, table 4.........table 40 and keep then to another table on daily basis. not all 47,00,000 data on regular basis just to want to take the updated only

Our database is old fashion and not so user friendly that's why this complexity happened.

So just make me sure there won't be any issue doing this query on regular basis to production server to make life easier :)

regards,


0 Votes 0 ·

Make sure there is an index on the column Id in all three tables. It should be okay.

0 Votes 0 ·
MohdOmarDaraz-1660 avatar image
0 Votes"
MohdOmarDaraz-1660 answered ·

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,

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MohdOmarDaraz-1660,

Thanks for your update.

Is there any primary key or clustered index in your single table called bm? If yes, you may have to consider the situation that duplicate records from different 40 tables will be inserted.

Since I do not have your situation, it is strongly recommended for you to create 40 triggers in one test server for a period of time to find out any issue before creating on a production server.

Best regards
Melissa

0 Votes 0 ·