使用存储过程在同一数据库中以一种方式同步两个表

I've 2 identical tables, one will be updated and the other no, so I want to write stored procedure to check the updates for existing record in table 2 and if not exist to insert the record from table 1 to table 2.

我有两个相同的表,一个将更新,另一个不,所以我想编写存储过程来检查表2中现有记录的更新,如果不存在则将表1中的记录插入到表2中。

Table 1

    | Field1 | Field2 | Field3 |
    | ------ | ------ | -----: |
    |  A     |  1     |   $10  |
    |  B     |  2     |   $20  |
    |  C     |  2     |   $21  |


Table 2

    | Field1 | Field2 | Field3 |
    | ------ | ------ | -----: |
    |  A     |  3     |   $13  |
    |  B     |  2     |   $20  |

What the stored procedure will do in this sample, it'll update row (B) in Table 2 with the data in Table 1 and insert row (C) to Table 2.

存储过程将在此示例中执行的操作,它将使用表1中的数据更新表2中的行(B)并将行(C)插入表2中。

2 个解决方案

#1

For the update part, you can check using the ID or any identifier field in your database:

对于更新部分,您可以使用数据库中的ID或任何标识符字段进行检查:

UPDATE t2 
SET t2.field2 = t1.field2 and t2.field3 = t1.field3 
FROM table2 AS t2
INNER JOIN table1 AS t1 
      ON t2.field1=t1.field1

And just add the insert part from the reply above after the update statement.

并且只需在update语句后面的回复中添加插入部分。

#2

Have you considered something like this?

你考虑过这样的事吗?

INSERT INTO Table2
(
     Field1
    ,Field2
    ,Field3
)
SELECT 
     Field1
    ,Field2
    ,Field3 
FROM Table1 
WHERE NOT EXISTS (
                  SELECT
                       Field1
                      ,Field2
                      ,Field3 
                  FROM Table2
                  )

Of course, if you have an identity seed, then you just take the MAX(Value) instead.

当然,如果你有一个身份种子,那么你只需要取MAX(值)。

#1