如果没有主键，id，date列存在，如何只获取2个表之间新插入的行 [英] How to get only newly inserted rows between 2 tables, if there is no primary key, id, date column exist
问题描述
让我们考虑： -
ServerA： - TableA（FName，MName，LName，Title）（表有数万亿条记录甚至更多）
ServerB： - TableB（FName，MName，LName，Title）（表有数万亿条记录甚至更多）
##没有这样的列存在 - 任何类型的密钥，id，createddate，modifieddate，no identity column
每天30分钟的停机时间新插入的记录从TableA同步到TableB
＃（Plz不要问这个逻辑，这是一个面试问题，所以我不知道同步逻辑）
案例： - 过去几天几个问题的Bcoz同步没有发生
问题： - 如何只从TableA获取新插入的记录以获得同步到TableB
条件： - 不想比较TableA&表B，重行比较它会花费太多时间。因此不需要进行比较。
请帮助识别TableB中不存在的新插入的行，其中逻辑 - >没有比较&不应该花太多时间的东西。考虑两个表来自DataWareHouse。
***请确保解决方案不应该比较两个表之间的行***
我尝试了什么：
我认为使用'except'设置运算符我们可以做到，但是因为重排，我不确定它将是智能解决方案/它将比较行，这是严格的面试不想要的。
第二我发现谷歌上的功能很少 - Scope_identity和更多。
但是他们只给出了最后插入的行。但是我希望所有这些行在同步作业失败的前几天内插入TableA。
Let's consider:-
ServerA:- TableA(FName,MName,LName,Title) (Table has trillions of records or even more)
ServerB:- TableB(FName,MName,LName,Title) (Table has trillions of records or even more)
## No such column exist- Any type of key, id, createddate, modifieddate, no identity column
Everyday for 30 min of downtime newly inserted records sync to TableB from TableA
#(Plz dont ask this logic, it's an interview question, so i m not aware with sync logic)
Case:- Bcoz of few issue for last few days sync not happened
Question:- How to get only newly inserted records from TableA to get sync to TableB
Condition:- Don't want to compare TableA & TableB, coz of heavy rows comparison it will take too much time. So comparison is not expected.
Please help to identify Only newly inserted rows from tableA which not exist in TableB, with logic-->without comparison & something which should not take too much time. Consider both tables are from DataWareHouse.
***Please make sure solution should not compare rows between both tables***
What I have tried:
I think using 'except' set operator we can do it, but coz of heavy rows, I am not sure it will be smart solution/ and it will compare rows, which is strictly interview dont want.
2nd I found few function on google like - Scope_identity and few more.
But they gave only last inserted row. But I want all those rows, which get insert to TableA in prev few days, when sync job failed.
推荐答案
一种方法是添加一个ROWVERSION列： rowversion（Transact-SQL） [ ^ ]
另一种是设置插入触发器和记录您在不同的表中插入的行，但如果无法唯一标识每一行（即某些描述的主键），则可能会在努力方面变得讨厌
One way to do it is to add a ROWVERSION Column:rowversion (Transact-SQL)[^]
Another would be to set set an insert Trigger and record the rows you insert in a different table, but that could get nasty in terms of effort if there is no way to uniquely identify each row (i.e. a primary key of some description)
这篇关于如果没有主键，id，date列存在，如何只获取2个表之间新插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
