Batch insert/update using stored procedure

Question

Can anyone give me a sample script for batch insert/update of records in table using stored procedure in SQL Server?

Answer 1

I've done something like this in the past:

CREATE PROCEDURE InsertProductIds(@ProductIds xml) AS

INSERT INTO Product (ID) 
SELECT ParamValues.ID.value('.', 'VARCHAR(20)')
FROM   @ProductIds.nodes('/Products/id') as ParamValues(ID) 

END

Obviously this is just a single-column table, but the XML approach applies for multi-column tables as well. You then do this:

EXEC InsertProductIds @ProductIds='<Products><id>3</id><id>6</id></Products>'