---
title: Inserting multiple records simultaneously in a table in SQL Server
description: Inserting (or updating) records one by one isn't that much of good idea. In such a case, we can send the data in XML format and use some built-in functions and stored procedures in SQL server to save the records.
image: https://blog.e-zest.com/hubfs/Sql-server.png
---

[![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/)

- [WORK](https://www.e-zest.com/our-work)
- [SERVICES](https://www.e-zest.com/our-services) 
    - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services)
    - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services)
    - [Digital Commerce](https://www.e-zest.com/digital-commerce-services)
    - [Digital Operations](https://www.e-zest.com/digital-operations)
    - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services)
    - [Digital Experience Design](https://www.e-zest.com/digital-experience-design)
- [INSIGHTS](https://blog.e-zest.com/)
- [CAREERS](https://www.e-zest.com/careers)
- [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services)
- [GENERATIVE AI](https://www.e-zest.com/generative-ai)
- [Healthcare](https://healthcare.e-zest.com)
- [Contact Us](https://www.e-zest.com/contact-us)

- ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg)

  [BLOGS](https://blog.e-zest.com)
  
  #### Elevating Product Engineering with AI and ML

  As technology evolves and customer choices broaden, there is an increasing ...

  [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml)
- ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg)

  [NEWS](https://blog.e-zest.com/news)
  
  #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024

  We are delighted to announce that e-Zest – An Accion Labs Company has been ...

  [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024)
- ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png)

  [CSR](https://blog.e-zest.com/csr)
  
  #### e-Zest Solutions supports Digital Pune Mission

  **Pune, India:** e-Zest Solutions, a leading digital technology innovation ...

  [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission)

- [Work](https://www.e-zest.com/our-work)
- [Services](https://www.e-zest.com/our-services)
- [Insights](https://blog.e-zest.com)
- [Careers](https://www.e-zest.com/careers)
- [Enterprise Data](https://www.e-zest.com/enterprise-data-services)
- [Generative AI](https://www.e-zest.com/generative-ai)
- [Innovations](https://www.e-zest.com/innovations)
- [Healthcare](https://healthcare.e-zest.com)
- [About](https://www.e-zest.com/about-us)
- [Contact](https://www.e-zest.com/contact-us)

- [Blog](https://blog.e-zest.com/)
- [News](https://blog.e-zest.com/news)
- [CSR](https://blog.e-zest.com/csr)

- [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/)
- [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest)
- [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638)
- [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/)
- [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate)

[![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server#top)

Hit enter to search or ESC to close

Insights

e-Zest members share technology ideas to foster digital transformation.

- [Blog](https://blog.e-zest.com)
- [Whitepaper](https://blog.e-zest.com/whitepaper)
- [Playbook](https://blog.e-zest.com/playbook)
- [News](https://blog.e-zest.com/news)
- [Innovation](https://blog.e-zest.com/innovation)
- [Media Kit](https://www.e-zest.com/media-kit)

# Inserting multiple records simultaneously in a table in SQL Server

[Sagar Damle](https://blog.e-zest.com/author/sagar-damle)  Dec 20, 2017

[SQL Server](https://blog.e-zest.com/topic/sql-server) [SQL](https://blog.e-zest.com/topic/sql)

- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server&title=&summary=&source=>
- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server&text=Inserting%20multiple%20records%20simultaneously%20in%20a%20table%20in%20SQL%20Server>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server>
- <https://www.instagram.com/ezestsolutions/?u=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server>

While developing applications, we may come across the situations where we need to insert/update multiple records in a database table at the same time. Inserting (or updating) records one by one isn't that much of good idea. In such a case, we can send the data in XML format and use some built-in functions and stored procedures in SQL server to save the records. Let’s create a sample web application demonstrating this using VS 2015 Express edition for Web.

Suppose there is a zoo and one of the staff members at the entry gate is required to make entries of visitors by providing their basic information (first name, last name,  
mobile number, age and ticket amount). User can save details of multiple visitors (one row for each visitor) if required; on single click of 'Save' button.

- Open Visual Studio and create a new empty MVC application.

(File → New Project... → Select “Installed” \>\> “Templates” \>\> “Visual C#” \>\> “Web” in the left pane in the opened window → Enter name for your web application(“InsertMultipleRecordsUsingXMLDemo”) → Click “OK” to navigate to next window → Select “Empty” as a template type with check marked checkbox for "MVC" → Click "OK")

- You can find a folder named "scripts" under the root folder of the application. Delete the JS files (if any) that are already present in it.

Download the jQuery library from-

https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js

Get Bootstrap's CSS and JavaScript from-

https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css  
and

https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js

As you can see, I am using v3.2.1 of jQuery and v3.3.7 of Bootstrap in this project.

\[You can also get these JS and CSS files from http://jquery.com/ (for jQuery) and http://getbootstrap.com/ (for Bootstrap).\]

Copy both the JS files (jQuery and Bootstrap) into "scripts" folder.

Create a new folder with name "Content" under the root folder of the application and copy Bootstrap's CSS file into it.

Add a new stylesheet file named "Style.css" in the same folder. Replace the original content of this file with the one given below-

.bottom-pad-10 {  
 padding-bottom: 10px;  
}

.top-pad-10 {  
 padding-top: 10px;  
}

#title {  
 padding: 5px;  
 font-weight: bold;  
 font-size: 30px;  
 color: #809fff;  
 background-color: #eeeeee;  
 border-radius: 4px;  
}

#dvInsertInfo th {  
 text-align: center;  
 color: white;  
 background-color: #4CAF50;  
}

#dvInsertInfo tbody tr {  
 text-align: center;  
 padding-top: 5px;  
 padding-bottom: 5px;  
}

 #dvInsertInfo tbody tr:nth-child(odd) {  
 background-color: #eaeffa;  
 }

- Inside "Views" folder, create a new folder named "Visitor". Add a new view with an empty template and without using any layout page in this newly created "Visitor" folder and name it as "InsertVisitors".

Put the content given below in this view to replace its default content.(Make sure that names and paths of CSS and JS files included in the \<head\> tag are correct.)-

@{  
 Layout = null;  
}

\<!DOCTYPE html\>  
\<html lang="en"\>  
\<head\>  
 \<meta charset="utf-8" /\>  
 \<meta name="viewport" content="width=device-width, initial-scale=1" /\>  
\<title\>Visitor Register\</title\>  
 \<link href="~/Content/bootstrap-3.3.7.min.css" rel="stylesheet" /\>  
 \<link href="~/Content/Style.css" rel="stylesheet" /\>  
 \<script src="~/scripts/jquery-3.2.1.min.js"\>\</script\>  
 \<script src="~/scripts/bootstrap-3.3.7.min.js"\>\</script\>  
\</head\>  
\<body\>  
 \<div class="container" id="dvContainer"\>  
 \<div class="text-center top-pad-10"\>  
 \<span id="title"\>Visitor Register\</span\>  
 \</div\>  
 \<div class="table-responsive top-pad-10 bottom-pad-10" id="dvInsertInfo"\>   
 \<table class="table"\>  
 \<thead\>  
 \<tr\>  
 \<th\>First Name\</th\>  
 \<th\>Last Name\</th\>  
 \<th\>Mobile No.\</th\>  
 \<th\>Age\</th\>  
 \<th\>Amount\</th\>  
 \</tr\>  
 \</thead\>  
 \<tbody\>  
 \<tr\>  
 \<td\>  
 \<input type="text" class="form-control info-field" /\>  
 \</td\>  
 \<td\>  
 \<input type="text" class="form-control info-field" /\>  
 \</td\>  
 \<td\>  
 \<input type="text" class="form-control info-field" /\>  
 \</td\>  
 \<td\>  
 \<input type="text" class="form-control info-field" /\>  
 \</td\>  
 \<td\>  
 \<input type="text" class="form-control info-field" /\>  
 \</td\>  
 \</tr\>  
 \</tbody\>  
 \</table\>  
 \</div\>  
 \<div class="top-pad-10 bottom-pad-10" id="dvAddDeleteRowButtons"\>  
 \<button type="button" id="btnAddRow" class="btn btn-primary"  
onclick="addNewRow()"\>Add New Row\</button\>  
\<button type="button" id="btnDeleteRow" class="btn btn-warning disabled"  
onclick="deleteRow()"\>Delete Last Row\</button\>  
 \</div\>  
\<div class="top-pad-10 text-center"\>  
 \<button type="button" id="btnSave" class="btn btn-success"onclick="saveAllVisitorInfo()"\>Save All\</button\>  
 \<button type="button" id="btnClear" class="btn btn-primary"  
onclick="clearAllVisitorInfo()"\>Clear All\</button\>  
 \</div\>  
 \</div\>  
 \<script\>  
 function addNewRow() {  
 var lastRow = $("#dvInsertInfo tbody tr").last();  
 var newRow = lastRow.clone();  
 newRow.find(".info-field").val("");  
 lastRow.after(newRow);  
 $("#btnDeleteRow").removeClass("disabled");  
 }  
 function deleteRow() {  
 var allRows = $("#dvInsertInfo").find("tbody tr");  
 var countRows = allRows.length;  
 if (countRows \> 1) {  
 allRows.last().remove();  
 }  
 if (countRows == 2) {  
 $("#btnDeleteRow").addClass("disabled");  
 }  
 }  
 function saveAllVisitorInfo() {  
 var propNames = \["FirstName", "LastName", "MobileNo", "Age",  
"TicketAmount"\];  
 var arrVisitor = \[\];  
 $.each($("#dvInsertInfo tbody tr"), function () {  
 var visitor = {};  
 $.each($(this).find(".info-field"), function (index) {  
 currentValue = $.trim($(this).val());  
 visitor\[propNames\[index\]\] = currentValue;  
 });  
 arrVisitor.push(visitor);  
 });  
$.ajax({  
 url: "@Url.Action("SaveVisitors", "Visitor")",  
 method: "POST",  
 data: { lstVisitor: arrVisitor },  
 success: function (response) {  
 if (response == 1) {  
 alert("Record(s) inserted successfully!");  
 if ($("#dvInsertInfo tbody tr").length \> 1) {  
 $("#dvInsertInfo tbody tr").slice(1).remove();  
 }  
 clearAllVisitorInfo();  
 $("#btnDeleteRow").addClass("disabled");  
 }  
 else {  
 alert("Some error has occurred while saving the records!");  
 }  
 },  
 error: function (xhrObj, strStatus, strError) {  
 alert(strError);  
 }  
 });  
 }  
 function clearAllVisitorInfo() {  
 $(".info-field").val("");  
 }  
 \</script\>  
\</body\>  
\</html\>

- We need to create a model class that will map information related to one entity (one visitor) to corresponding row in the database table. For that, add a class ("Visitor.cs") in the "Models" folder.

Delete the auto-generated code and put the one given below-  
using System;  
namespace InsertMultipleRecordsUsingXMLDemo.Models  
{  
 public class Visitor  
 {  
 public string FirstName { get; set; }  
 public string LastName { get; set; }  
 public int MobileNo { get; set; }  
 public int Age { get; set; }  
 public int TicketAmount { get; set; }   
 }  
}  
Create a new empty controller with name "VisitorController" in "Controllers" folder. Replace its content with this one-

using System;  
using System.Collections.Generic;  
using System.Configuration;  
using System.Data;  
using System.Data.SqlClient;  
using System.Text;  
using System.Web.Mvc;  
using System.Xml;  
using InsertMultipleRecordsUsingXMLDemo.Models;  
namespace InsertMultipleRecordsUsingXMLDemo.Controllers  
{  
 public class VisitorController : Controller  
 {  
 public ActionResult InsertVisitors()  
 {  
 return View();  
 }  
 \[HttpPost\]  
 public int SaveVisitors(List\<Visitor\> lstVisitor)  
 {  
 int result = 0;

 StringBuilder lSb = new StringBuilder();  
 XmlWriterSettings lXmlSettings = new XmlWriterSettings { OmitXmlDeclaration = true, ConformanceLevel = ConformanceLevel.Fragment };  
 XmlWriter lXmlWriter = XmlWriter.Create(lSb, lXmlSettings);  
 lXmlWriter.WriteStartElement("rows");  
 foreach (var visitor in lstVisitor)  
 {  
 lXmlWriter.WriteStartElement("row");  
 lXmlWriter.WriteElementString("FirstName", visitor.FirstName);  
 lXmlWriter.WriteElementString("LastName", visitor.LastName);  
 lXmlWriter.WriteElementString("MobileNo", Convert.ToString(visitor.MobileNo));  
 lXmlWriter.WriteElementString("Age", Convert.ToString(visitor.Age));  
 lXmlWriter.WriteElementString("TicketAmount", Convert.ToString(visitor.TicketAmount));  
 lXmlWriter.WriteEndElement();  
 }  
 lXmlWriter.WriteEndElement();  
 lXmlWriter.Flush();  
lXmlWriter.Close();  
 string sqlConString = ConfigurationManager.ConnectionStrings\["ConnString"\].ConnectionString;

 try  
 {  
 using (SqlConnection con = new SqlConnection(sqlConString))  
 {  
 using (SqlCommand cmd = new SqlCommand("InsertVisitors\_prc",con))  
 {  
 cmd.CommandType = CommandType.StoredProcedure;  
 cmd.Parameters.AddWithValue("@MultipleRecordsData",  
lSb.ToString());  
 con.Open();  
 cmd.ExecuteNonQuery();  
 con.Close();  
 }  
 }  
 result = 1;  
 }  
 catch (Exception ex)  
 {  
 System.Diagnostics.Trace.WriteLine(ex.Message); //You can see the exception message in Output window (Go to "View" in menu bar and select "Output").  
 result = 0;  
 }  
 return result;  
 }  
 }  
}  
If you see, there is a statement-  
string sqlConString = ConfigurationManager.ConnectionStrings\["ConnString"\].ConnectionString;  
For this, you need to have \<connectionStrings\> tag as a child node of \<configuration\> in the application's main Web.config file (This file is present directly under the root folder).  
The file should look something like this-

\<?xml version="1.0" encoding="utf-8"?\>  
\<configuration\>  
\<connectionStrings\>  
\<add name="ConnString" connectionString="Data Source=SERVER\_NAME; InitialCatalog=DATABASE\_NAME; User ID=YOUR\_USER\_ID; Password=YOUR\_PASSWORD;"  
providerName="System.Data.SqlClient" /\>  
\</connectionStrings\>  
\<!-- Other part has been removed for brevity. --\>  
\</configuration\>

- Create a table named "Visitor" in database to save the records.

USE \[DATABASE\_NAME\] --Put your database name here.  
GO  
CREATE TABLE Visitor  
( Id INT IDENTITY(1,1) PRIMARY KEY,  
 \[FirstName\] \[VARCHAR\](50) NULL,  
 \[LastName\] \[VARCHAR\](50) NULL,  
 \[MobileNo\] \[VARCHAR\](20) NULL,  
 \[Age\] \[SMALLINT\] NULL,  
 \[TicketAmount\] \[SMALLINT\] NULL )

GO

Now create the stored procedure "InsertVisitors\_prc" that is the heart of this blog.

CREATE PROCEDURE \[dbo\].\[InsertVisitors\_prc\]  
@MultipleRecordsData VARCHAR(MAX)  
AS  
BEGIN  
DECLARE @idoc INT

EXEC sp\_xml\_preparedocument @idoc OUTPUT, @MultipleRecordsData   
SELECT \* INTO #Data FROM  
OPENXML(@idoc, '/rows/row', 2) WITH  
(  
\[FirstName\] VARCHAR(50),  
\[LastName\] VARCHAR(50),  
\[MobileNo\] VARCHAR(20),  
\[Age\] SMALLINT,  
\[TicketAmount\] SMALLINT  
)  
EXEC sp\_xml\_removedocument @idoc  
INSERT INTO Visitor (\[FirstName\], \[LastName\], \[MobileNo\], \[Age\], \[TicketAmount\])  
SELECT \[FirstName\], \[LastName\], \[MobileNo\], \[Age\], \[TicketAmount\] FROM #Data  
END  
GO  
The operations that we perform in this stored procedure are-

a) Create a tree representation of nodes from input XML text into internal cache of SQL Server using system-defined stored procedure "sp\_xml\_preparedocument".

b) Get rowset from it using element-centric mapping (Note that 3rd argument for OPENXML is '2' that represents element-centric mapping) into a temporary table\[#Data\].

c) Free up the memory used by the rowset in step b) (We don't need it because now we have the data in the temporary table.) using another system-defined stored procedure "sp\_xml\_removedocument".

d) Insert the records from \[#Data\] into table \[Visitor\].

Now build and then run the application. Before running it, just make sure that you have set default controller and action names in "RouteConfig.cs" file properly as given below-  
(You can find the file in the folder "App\_Start" that is under the root folder of the application.)  
using System.Web.Mvc;  
using System.Web.Routing;  
namespace InsertMultipleRecordsUsingXMLDemo  
{  
 public class RouteConfig  
 {  
 public static void RegisterRoutes(RouteCollection routes)  
 {  
 routes.IgnoreRoute("{resource}.axd/{\*pathInfo}");  
 routes.MapRoute(  
 name: "Default",  
 url: "{controller}/{action}/{id}",  
 defaults: new { controller = "Visitor", action = "InsertVisitors", id = UrlParameter.Optional }  
 );  
 }  
 }  
}

On 'Visitor Register' page, you can add information about more than one visitors by creating new row(s) for each of them by clicking 'Add New Row' button.

After clicking on 'Save All' button, you can check for the records inserted in the database by executing following query in SQL server-

SELECT \* FROM Visitor

(You may have observed that I haven't put any attention to validating user inputs, simply because the purpose of the blog is "how" to insert multiple recordssimultaneously rather than considering "what" type of data is being inserted. But in real world, there must be validations for the content before insertion.)

If you want to dive deep into the code, please read more on the web about different classes like XmlWriter() and XmlWriterSettings() that are present in System.Xml namespaces. Also, it is worth to have a look at sp\_xml\_preparedocument, OPENXML and sp\_xml\_removedocument.

As a further study, you also can read about MERGE statement in SQL server. It can be used in "update if record is already present and insert if it is not" situations.

- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server&text=Inserting%20multiple%20records%20simultaneously%20in%20a%20table%20in%20SQL%20Server>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server>
- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server&title=&summary=&source=>

### Similar Blog

[![Just-in-Time Cloud Resources](https://blog.e-zest.com/hubfs/blog-files/just-in-time-cloud-resources.png) Just-in-Time Cloud Resources To date, there exists many methods to schedule the start and stop of cloud resources for an application. If it can be known when ... By Sneha Sajjan Nov 22, 2018](https://blog.e-zest.com/just-in-time-cloud-resources)

[![Full-Text Search in SQL Server](https://blog.e-zest.com/hs-fs/file-3395400579-png/blog-files/table.png) Full-Text Search in SQL Server In this blog I would like to share some of my interesting findings in Database with Full-Text Search using C#.NET (You can ... By Amit Vengurlekar Dec 30, 2014](https://blog.e-zest.com/full-text-search-in-sql-server/)

[![Dynamic Generation of PDF File Using iTextSharp](https://blog.e-zest.com/hubfs/Dynamic%20generation%20of%20PDF%20file%20using%20iTextSharp.jpg) Dynamic Generation of PDF File Using iTextSharp Many a times, we need to generate PDF files at runtime in our application. Here, 'iTextSharp' (a third party DLL) can help us to ... By Sagar Damle Oct 31, 2017](https://blog.e-zest.com/dynamic-generation-of-pdf-file-using-itextsharp)

## Helping Organizations Thrive in the Digital Age

[Contact Us](https://www.e-zest.com/contact-us)

- [About](https://www.e-zest.com/about-us)
- [Services](https://www.e-zest.com/our-services)
- [Work](https://www.e-zest.com/our-work)
- [Insights](https://blog.e-zest.com/)
- [Healthcare](https://healthcare.e-zest.com/)
- [Accion Labs](https://www.accionlabs.com/)

- <https://www.linkedin.com/company/e-zest-solutions/>
- <https://twitter.com/ezest>
- <https://www.facebook.com/eZest/>
- <https://www.instagram.com/ezestsolutions/>
- <https://www.youtube.com/user/ezestcorporate>

- Dallas
- Detroit
- Hannover
- Vienna
- London
- Pune

 © 2025 e-Zest Solutions

- [Privacy Policy](https://www.e-zest.com/privacypolicy)
- [Cookie Policy](https://www.e-zest.com/cookie-policy)

 Contact us

 Contact us

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.