SQL multi save without using loop/ cursor

I have a requirement where from a string of int[] I need to insert all the values of this array into DB. I have tried this solution and it works fine.

To achieve this I have converted the int[] to XML in C#, and in SP used this XML variable to fill a table variable and looped over this table variable to do the insert.

Is there any way to avoid the while loop and insert multiple values comma separated string into the table ?

Below is the code :

  DECLARE @CatTable TABLE(RowID int not null primary key identity(1,1), ID int)
  
    DECLARE @RowsToProcess  int
    DECLARE @CurrentRow     int
    
 INSERT INTO @Table
    SELECT tbl.colname.value('text()[1]','int') AS Id
    FROM @IdsList.nodes('/List/CIds') tbl(colname);
  
    SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0

WHILE @CurrentRow<@RowsToProcess    --- **How to avoid this loop and do the insert ?**
BEGIN
    SET @CurrentRow=@CurrentRow+1

        
    INSERT INTO tblRealtable (id, anotherId)
    SELECT ID ,@AnotherID  FROM @Table
        WHERE RowID=@CurrentRow
    
END
enD
END

Answer

Aren’t you just after this?

INSERT INTO dbo.tblRealtable (id, anotherId)
SELECT tbl.colname.value('text()[1]','int') AS Id
       @AnotherID
FROM @IdsList.nodes('/List/CIds') tbl(colname);

Advertisement

Answer