Inserting in bulk

If you come across a situation whereby you want to insert records in bulk, here is how you can do it.

Create table #tblCategory (CategoryId int identity(1,1),CategoryName nvarchar(50))

DECLARE @xmlString XML

SET @xmlString = '<Categories>
<Category>
<CategoryName>Administration</CategoryName>
</Category>
<Category>
<CategoryName>Security</CategoryName>
</Category>
<Category>
<CategoryName>Accounting</CategoryName>
</Category>
<Category>
<CategoryName>Marketing</CategoryName>
</Category>
</Categories>'

INSERT INTO #tblCategory (CategoryName)
SELECT ParamValues.ID.value('CategoryName[1]','NVARCHAR(50)') AS CategoryName
FROM @xmlString.nodes('//Categories/Category') as ParamValues(ID)

SELECT CategoryId,CategoryName FROM #tblCategory

DROP TABLE #tblCategory

The results:

CategoryId	CategoryName
1	Administration
2	Security
3	Accounting
4	Marketing

This can come in handy when, lets say, you have a DataGrid and you can select more than one record and you want to save those records to the database. You can create an Xml string using a StringBuilder, then parse the xml string as a parameter to your insert statement, provided you are using a stored procedure.

Hopes this will help, and if you have any suggestions, additions or subtractions you are welcome to add them.