添加计数器以计算行数将插入表中

I'm trying to do an insert statement, but I am checking if the data has been inserted before, and if so, it will not insert the row in the table. After this is complete, I want to return the number of rows that actually were inserted. Can anyone help with adding a counter? I'm using SQL Server 2012.

我正在尝试执行insert语句,但我正在检查之前是否已插入数据,如果是,则不会在表中插入行。完成后,我想返回实际插入的行数。任何人都可以帮忙添加一个柜台吗?我正在使用SQL Server 2012。

My query is as follows :

我的查询如下:

        If NOT EXISTS (Select Id from @List where Id = cast(@IG_Id as int))
BEGIN  
       SET @Sql = 
          'INSERT INTO '+@List+' (Id,Id,LCode,CellNumber,WorkNumber,HomeNumber)
           VALUES ('''+@CId+''','''+@LId+''','''+@LCode+''','''+@MobileNumber+''','''+@BusinessNumber+''','''+ @HomeNumber+''')'
           print @Sql END

1 个解决方案

#1

try this

If NOT EXISTS (Select Id from @List where Id = cast(@IG_Id as int))
BEGIN 
    INSERT INTO @List (Id,Id,LCode,CellNumber,WorkNumber,HomeNumber)
    VALUES (@CId, @LId, @LCode, @MobileNumber, @BusinessNumber, @HomeNumber)
END 

SELECT @@ROWCOUNT as NumberOfInsertedRows

actually, since you have only one list of values to insert, @@ROWCOUNT can be only 0 or 1

实际上,由于您只有一个要插入的值列表,因此@@ ROWCOUNT只能为0或1

edit: to show how to insert from another table

编辑:显示如何从另一个表插入

INSERT INTO @List (Id,Id,LCode,CellNumber,WorkNumber,HomeNumber)
select Id,Id,LCode,CellNumber,WorkNumber,HomeNumber
from [MyDataSetTable]
where CAST([MyDataSetTable].IG_Id as int) not in (select Id from @List)

SELECT @@ROWCOUNT as NumberOfInsertedRows

#1