如果不是EXISTS，请插入 [英] IF NOT EXISTS , insert into

问题描述

我在SQL中有2个表。 table1和table2。我需要只比较这两个表中的一列并返回表2中的缺失列（在我的情况下是500行）然后将此列与其他值一起插入表2.我可以得到不存在的列以下代码：



从学生中选择stud_code

其中stud_code不在（从portalusers中选择portal_code，其中prodcode ='SC'）

和cmpy_code'01';和dol是空的

由student.stud_code订购



这将返回500行，我现在需要遍历结果集并插入他们进入表2.但问题是这两个表只有一个共同的列，它在reult集中返回，我必须以某种方式将行插入表2（@stud_code，val2，val 3，val4）。有什么建议吗？



我尝试了什么：



我不喜欢我知道如何遍历结果集并将它们插入到具有不同列的新表中。

I have 2 tables in SQL. table1 and table2. I need to compare only one column of these two tables and return the missing ones in tables 2( which in my case in 500 rows) then insert this column along with other values into the table 2. I could get the the not existing columns with the following code:

select stud_code from student
where stud_code NOT in ( select portal_code from portalusers where prodcode='SC')
and cmpy_code'01'; and dol is null
order by student.stud_code

this will return 500 rows, I need now to loop through the result set and insert them into table 2. but the problem is these 2 tables only have one column in common which is returned in reult set, I have to insert the row into the table 2 somehow like (@stud_code, val2, val 3, val4). any suggestions?

What I have tried:

I don't know how to loop through the result set and insert them into the new table with different columns.

推荐答案

我不确定你的意思...你能不能不要

I'm not sure what you mean ... can you not do

insert into table2 (stud_code, val2, val3, val4)
select stud_code, val2, val3, val4
from table1
where stud_code NOT in ( select portal_code from portalusers where prodcode='SC') 
and cmpy_code'01'
and dol is null
order by student.stud_code

您可能需要围绕选择的方括号，但这就是想法 - 很明显，如果你在表2中插入4个值，那么你的选择查询必须提供4个值



当你开始时，那很好，总是测试'选择'然后把它扩展到插入... select子句，只是让你知道你得到了什么

you may need brackets around the select but thats the idea - obviously if you're saying insert 4 values into table2, your select query has to supply 4 values

As you've started, thats great, always test the 'select' bit before you then extend it to the insert into ... select clause, just so you know what you're getting

非常感谢Garth。这就是我所需要的。我想我的想法太深了，并且与c＃编程混在一起：）

thank you very much Garth. that's what I exactly needed. I think I was thinking too deep and was mixed up with c# programming :)

这篇关于如果不是EXISTS，请插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！