svr1 tab1
ser2 tab2
打開svr1的查詢分析器查詢tab1的數據是否在svr2的tab2裡,如類似
select sn from tab1 where sn in (use ser2 select sn from tab2)
select sn from tab1 where sn in (select sn from ser2..tab2)
ser2 tab2
打開svr1的查詢分析器查詢tab1的數據是否在svr2的tab2裡,如類似
select sn from tab1 where sn in (use ser2 select sn from tab2)
select sn from tab1 where sn in (select sn from ser2..tab2)
select sn from tab1 where sn in (后面是从集合里面读数据生成一个SQL语句)
sn = sn & rs.fileds("sn") & ","
rs.movenext
looprs.closeif sn <> vbnullstring then sn = mid(sn,1,len(sn)-1)rs.open "select sn from tab1 Where " & TransInOut("sn", sn, True),ser1, adOpenDynamic, adLockOptimistic
‘函数如下 blInType 为FALSE表示“Not In”;为True表示“In”
Public Function TransInOut(ByVal sKeyCode As String, _
ByVal sCodeSql As String, _
Optional blInType As Boolean = True) As String
Dim sExp As String
Dim sInOut As String Dim iCount As Long
Dim sCodeIn As String
Dim Index1 As Long
Dim Index2 As Long If blInType Then
sExp = " or "
sInOut = " in "
Else
sExp = " and "
sInOut = " not in "
End If Index1 = 0
Do
iCount = iCount + 1
Index2 = Index1 + 1
Index1 = InStr(Index2, sCodeSql, ",")
If Index1 <= 0 Then Exit Do If iCount = 999 Then
If sCodeIn <> "" Then sCodeIn = sCodeIn & sExp
sCodeIn = sCodeIn & sKeyCode & sInOut & "(" & Left(sCodeSql, Index1 - 1) & ")"
sCodeSql = Mid(sCodeSql, Index1 + 1)
Index1 = 0
iCount = 0
End If
Loop If sCodeSql <> "" Then
If sCodeIn <> "" Then sCodeIn = sCodeIn & sExp
If Right(sCodeSql, 1) = "," Then
sCodeIn = sCodeIn & sKeyCode & sInOut & "(" & Mid(sCodeSql, 1, Len(sCodeSql) - 1) & ")"
Else
sCodeIn = sCodeIn & sKeyCode & sInOut & "(" & sCodeSql & ")"
End If
End If
TransInOut = "(" & sCodeIn & ")"
End Function
select sn from tab1 where sn in (select sn from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=另一台机器名;User ID=sa;Password='
).数据库名.dbo.tab2)