如何比较两个表并在实体或SQL中插入其他表? [英] How to compare two table and insert other table in entity or SQL?
问题描述
如何比较不同的数据库表和如果两列不相同,添加到其他表。然后如果不相等状态得到0.
我尝试了什么:
How to compare different database table and If two columns are not identical, add to other table.And then if not equal status get 0.
What I have tried:
DATABASE STUDENT -TABLE STUDENT
ID NAME COUNTRYNO AGE BRANCHCODE
1 Alex 185 25 15
2 Mary 146 26 19
3 Emily 102 45 14
DATABASE PERSON - TABLE PERSON
ID NAME COUNTRYNO AGE BRANCHCODE
1 John 127 45 65
2 Elize 125 54 39
3 Toms 127 42 94
<pre>I would like to look at two columns and two columns, and then look at the country no and the branchcode that are in the person. I want to insert into the table named NewTable if these two columns are different.
DATABASE STUDENT - NEW STUDENT
ID NAME COUNTRYNO AGE BRANCHCODE DURUM
1 John 127 45 65 0
2 Elize 125 54 39 0
3 Toms 127 42 94 0
var common =query1.Intersect(query2);
string query= "SELECT COUNTRYNO,BRANCHCODE FROM " + DATABASE.SCHEMA + ".dbo.STUDENT";
string query2= "SELECT COUNTRYNO,BRANCHCODE FROM " + DATABASE.SCHEMA + ".dbo.PERSON";
How can I do with sql query or entity (add range).
推荐答案
MERGE语句会这样做(基于你的有限 - 和荒谬 - 示例数据集)。
A MERGE statement would do it (based on your limited - and nonsensical - example data set).
MERGE table1 AS t
USING table2 AS s
ON (t.id = s.id)
WHEN MATCHED THEN
SET t.name = s.name,
t.countryno = s.countryno,
t.age = s.age,
t.branchcode = s.branchcode
WHEN NOT MATCHED
INSERT (name, countryno, age, branchcode)
VALUES (s.name, s.countryno, s.age, s.branchcode);
上面的代码将更新任何行目标表中的ID与源表中的ID匹配。否则,如果找不到匹配项,源行将被添加到目标表。
如果我的例子,你将最终得到三个记录目标表。
CAVEAT:我输入了这个,所以可能需要进行一些小的调整来实际执行。
The code above will update any row where the ID from the target table matches the one from the source table. Otherwise, if a match isn't found, the source row will be added to the target table.
If my example, you will end up with three records in the target table.
CAVEAT: I typed this off the top of my head, so it may need some minor tweaking to actually execute.
这篇关于如何比较两个表并在实体或SQL中插入其他表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!