ASP，对比SQL两表，取差异存入新表。

来源：百度知道 编辑：UC知道 时间：2024/05/27 12:22:36
目的：取pic里news_uploadpic没有的picname存入delpic的picname字段。
pic表中的picname比news_uploadpic里的前面多出“uploadfile/”
<%
Set Conn=Server.CreateObject("ADODB.Connection")
ConnStr = "driver={SQL Server};server=(local);uid=tw0902;pwd=jintian***8;database=legion"
conn.open ConnStr
a = "Select picname From pic"
b = "Select picname From news_uploadpic"
c = "Select picname From delpic"
set rs=server.createobject("adodb.recordset")
set rs2=server.createobject("adodb.recordset")
rs.open a,conn,1,3
rs2.open b,conn,1,3
if (a<>"uploadfile/"+b) then
set rs3=server.createobject("adodb.recordset")
rs3.open c,conn,1,3
rs3.addnew
rs3("picname")=a
rs3.update
end if
set rs=nothing
set rs2=nothing
set rs3=nothing
set conn=nothing
%>
初学，很多都是自己凭空写的，怎么想怎么写，请会的朋友加以修正，或者干脆另写一个A

只需一条SQL就能搞定:
a="insert into delpic select picname from pic where picname not in(select 'uploadfile/' + picname picname from news_uploadpic)"

不过你要注意:这个语句只能执行一次,不能多次执行,否则会造成delpic表记录重复.所以要用以下语句多次执行都没有问题:
a="insert into delpic select picname from pic where picname not in(select 'uploadfile/' + picname picname from news_uploadpic union select picname from delpic)"

以上语句还有一个问题就是效率问题,如果表中记录不是很多也不影响.

<%
Set Conn=Server.CreateObject("ADODB.Connection")
ConnStr = "driver={SQL Server};server=(local);uid=tw0902;pwd=jintian***8;database=legion"
conn.open ConnStr
a = "insert into delpic(picname)(select 'uploadfile/'+ picname from pic where picname not in(select picname from news_uploadpic))"
set rs=server.createobject("adodb.recordset")
rs.open a,conn,1,3
end if
set rs=nothing
set conn=nothing
%>

这样就可