如何仅将最近更新的行从Table1插入到2 [英] How Do I Insert Only Recently Updated Row From Table1 To 2

问题描述

我有两个具有相同架构的表。我必须检查table1是否有任何更新，并将更新或整行插入table2，只有最近更新的行。

所以基本上，table2具有table1的唯一值;对于table1中的重复条目，table2中只有最新的条目。

i试图从table1获取最近更新的id：

i have two tables with same schema. i have to check table1 for any updates and insert the updates or the entire row into table2, only the most recently updated row.
so basically, table2 has the unique values from table1; for the duplicate entries in table1, there is only the most recent entry in table2.
i have tried to get the most recently updated id from table1:

SELECT distinct id
FROM table1 order by time_date desc limit 1;

现在我需要使用此id从table1获取其他列并插入行带有那个id，进入table2。

我必须为table1中的所有条目执行此操作。



任何帮助都会很棒。 ！

谢谢。

now i need to use this id to get the other columns from table1 and insert the row with that id, into table2.
and i have to do this for all the entries in table1.

any help would be great.!
thank you.

推荐答案

在`table1`中设置一个标志以标记该行是否已被更好的方法更新到`table2`或不。这很容易做到，比你目前找到最近一行的方法更方便（`最近的'对我来说是想象的。）



然而，一旦你得到了来自`table1`的最近一行的id，你可以将行插入`table2`就像 -

It would be a better approach to have a flag in `table1` to mark whether the row has been updated to the `table2` or not. That's pretty easy to do and much more convenient than your current approach of finding the recent row (`recent` sounds imaginary to me).

However, once you got the id of the recent row from `table1`, you can insert the row to the `table2` like-

INSERT INTO table2
SELECT * FROM table1 WHERE id=@id

希望，那帮助:)



参考文献：

SQL INSERT INTO SELECT语句 [ ^ ]

最好的方法是使用MERGE语句。

The best way is to use the MERGE Statment.

DECLARE @int int 

--*  The line below will allow you to insert identy or primary id key 
SET IDENTITY_INSERT table1 ON

--* now the merge statement
--* t is the target table
--* s is the source table

MERGE table1 t
      USING table2 s 
      ON s.primkey = t.primekey
      WHEN MATCHED 
           THEN UPDATE
                SET t.primkey = s.primkey
                   ,t.col2name = s.col2name
                   ,t.col3name = s.col3name
                   ,t.col4name = s.col4name
       WHEN NOT MATCHED
           THEN INSERT
                ( t.primekey
                 ,t.col2name
                 ,t.col3name
                 ,t.col4name)
            VALUES
                ( s.primkey
                 ,s.col2name
                 ,s.col3name
                 ,s.col4name); 

--* turn Identiy insert off 
SET IDENTITY_INSERT table1 OFF

--*Now update the sequence of the primarykey id field.
SET @int = (SELECT MAX(t.primkey) as intseed from dbo.table1)

--* using the @int update table1 identity sequence.
dbcc checkident('table1', Reseed, @int)

您不必在ju上合并st主键。您可以合并多个参数。我刚刚使用了我的例子中最简单的。



有关合并的更多信息，请参阅以下网站。这是非常强大的。



https：/ /msdn.microsoft.com/en-us/library/bb510625.aspx [ ^ ]

You don't have to merge on just the Primary key. You can merge on multiple statments. I just used the simplest in my example.

See the following site for more information on merge. It is very powerful.

https://msdn.microsoft.com/en-us/library/bb510625.aspx[^]

这篇关于如何仅将最近更新的行从Table1插入到2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！