如果不存在就添加:if not exists(select * from 表 where .....) insert 表 values (........)
sql server中有数组吗?我怎么不知道
这个要求基本不能实现。可以这样做: create proc sp_InsertNode @nodeid int as if not exists(select nodeid from nodetable where nodeid=@nodeid) begin insert nodetable(nodeid) values(@nodeid) end 然后循环取出单个元素调用sp_InsertNode
組SQL的過程,應該放到前端去做才有可能實現。因為T-SQL根本就不支持數組。 一個小示例,可供參考。(VB)Dim ary(10) As String Dim strSQL As String Dim tmpSQL As String Dim idx As LongFor idx = 0 To 10 ary(idx) = CStr(idx + 1) Next idxstrSQL = "Select " & Join(ary, " as MM Union All Select ") & " " tmpSQL = " Insert Into AnthoerTable (ColXX) " & vbCrLf & _ " Select tmp.MM From (" & vbCrLf & _ strSQL & " ) tmp " & vbCrLf & _ " LEFT JOIN AnthoerTable b " & vbCrLf & _ " ON tmp.MM=b.ColXX " & vbCrLf & _ " Where b.ColXX IS NULL "這時組出來的tmpSQL就可以直接發送到SQL Server上去執行了。
其实就是把数组组合成逗号分隔的字符串传递给存储过程'vbs of asp format strSql="exec usp_??? " & join(ary,',') cn.execute strSql--proc create proc usp_??? @ary nvarchar(4000) as declare @str nvchar(6000) set @str='insert NodeTable Select * from SourceTable where id not in ('+@ary +')' exec sp_executesql @str return @@rowcount还有一种理解,如果你是要从客户生成插入数据的话,那就在客户端组织一个长的insert .. select .. 语句吧,就像 N_chow(一劍飄香) 那样就可以的。
insert 表 values (........)
create proc sp_InsertNode @nodeid int
as
if not exists(select nodeid from nodetable where nodeid=@nodeid)
begin
insert nodetable(nodeid) values(@nodeid)
end
然后循环取出单个元素调用sp_InsertNode
一個小示例,可供參考。(VB)Dim ary(10) As String
Dim strSQL As String
Dim tmpSQL As String
Dim idx As LongFor idx = 0 To 10
ary(idx) = CStr(idx + 1)
Next idxstrSQL = "Select " & Join(ary, " as MM Union All Select ") & " "
tmpSQL = " Insert Into AnthoerTable (ColXX) " & vbCrLf & _
" Select tmp.MM From (" & vbCrLf & _
strSQL & " ) tmp " & vbCrLf & _
" LEFT JOIN AnthoerTable b " & vbCrLf & _
" ON tmp.MM=b.ColXX " & vbCrLf & _
" Where b.ColXX IS NULL "這時組出來的tmpSQL就可以直接發送到SQL Server上去執行了。
strSql="exec usp_??? " & join(ary,',')
cn.execute strSql--proc
create proc usp_???
@ary nvarchar(4000)
as
declare @str nvchar(6000)
set @str='insert NodeTable
Select * from SourceTable where id not in ('+@ary
+')'
exec sp_executesql @str
return @@rowcount还有一种理解,如果你是要从客户生成插入数据的话,那就在客户端组织一个长的insert .. select .. 语句吧,就像 N_chow(一劍飄香) 那样就可以的。