SQL Server仅基于多个列插入新行(SQL Server insert new rows ONLY based on multiple columns)

 我在SO搜索但是找不到任何符合我目的的东西。 我只需要从一个表插入另一个表中的唯一行。 我有： 
 
 表格1 
 
id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1331   elm       04/01/2016  04/30/2016 05/01/2016 5656
 
 表2 
 
id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1441   elm       04/01/2016  04/30/2016 05/01/2016 5656
 
 我需要在table2中插入table = id = 2的行，并从table1中删除id = 2的行，因为即使其余列匹配，bookid也是不同的。 我试过以下： 
 
insert into table1
select * from table2
where not exists (select * from table2 where table1.id = table2.id
and table1.name = table2.name and table1.bookid = table2.bookid and
table1.bookname = table2.bookname and table1.start_date =  table2.start_date
and table1.end_date = table2.end_date and table1.rel_date =            table2.rel_date
and table1.rel_id = table2.rel_id)
 
 我可以在一个sql块中以任何方式完成所有这些操作吗？ 

I searched in SO but couldn't find anything for my purpose. I need to insert unique rows ONLY from one table into another. I have:
 
table1
 
id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1331   elm       04/01/2016  04/30/2016 05/01/2016 5656
 
table2
 
id  name    bookid bookname start_date   end_date   rel_date   rel_id
1   horror  1221   rockys    04/01/2016  04/30/2016 05/01/2016 4545
2   horror  1441   elm       04/01/2016  04/30/2016 05/01/2016 5656
 
I need to insert into table1 the row with id = 2 in table2 AND also delete the row with id = 2 from table1, because bookid is different even though the rest of the columns match. I tried following:
 
insert into table1
select * from table2
where not exists (select * from table2 where table1.id = table2.id
and table1.name = table2.name and table1.bookid = table2.bookid and
table1.bookname = table2.bookname and table1.start_date =  table2.start_date
and table1.end_date = table2.end_date and table1.rel_date =            table2.rel_date
and table1.rel_id = table2.rel_id)
 
Any way I can do all of this in one sql block?

原文：https://stackoverflow.com/questions/37146915