Synchronizing data between two tables in a certain way

Transact-SQL
1

PostgreSQL (PgAdmin 4). I have two tables. I add data to the first table time by time using insert for all columns.

Table1:

CREATE TABLE purchases (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
price NUMERIC,
about VARCHAR )

Table2:

CREATE TABLE result (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
profit NUMERIC,
sold NUMERIC )
  1. First table is as list (history) of purchases and many rows would be added there. I want my second table to check if there in the first table added any new (not existing in Table2) names and add new row to my Table2 with such name (name is a field in my tables)
  2. If i put in my first table two rows:

a) name - cheese, qty - 20

b) name - cheese, qty - 20

Then can i do thing that in my empty Table2 must appear the row: name - cheese, qty - 40 (sum of qtyS from my Table1) In the first table won't be only cheese but many other product. I just want to add only new rows to my second table and if there appears a new row in Table1 with existing name in Table2 with any quantity - then i update certain row in Table2 with new total sum of product.

I am starting my way to this and I would be souly gratefull to those who help me))
Please, provide your answers wide & fully if u are able to do it!

2

Welcome and happy learning journey

Why do you need the sexons table for? And do you want it to be updated in an automated fashion ?

3

What is sexons table. I want to do it like updates every time Table1 changes.
Or if u can help to do it with a view it ill be also nice. Ur choice i mnot so flex to give such tip imho))

4

sorry mistyped. I meant to say the second table :laughing: what will it be used for?

 select sum(qty), name
 From purchases
 group by name

Depends if you want the sum to be b date, by month, by year or sum period

5

I know how to select data from another table but i have some problems here. When i add new row to Table1:

  1. If the NAME (field) of new row in table1 exists in view than just sum the qty of this new row to row qty with such name in view
  2. if the NAME of new row in table1 doesnt exist in view than create new row with same qty as in Table1 new row

And i want to add one more table (there will be 2 other tables and a VIEW (table2 now) and view trigers if either one or another tables changes. And after that how to check which table of them changed before any other statement to do in view (Table2) starts.

6

  1. stored procedure
    you can create a stored procedure with code that is run every time you add data

  2. Trigger
    you can create a trigger on the source table that is run every time you add data

  3. Merge Statement
    tsql - SQL Server - synchronizing 2 tables on 2 different databases - Stack Overflow

  4. Custom software
    There are special software that do this will all sort of options and automations
    may be an overkill
    dbForge Data Compare = Devart

1 Like