Today in this post I will show you how to copy multiple data into database table at a time. I am sure you have wrote single INSERT statement to insert a single row to database. Or you have used a for loop or a foreach loop to run multiple insert query. But apart form all these in this post I will show you how to insert data into database table in bulk at a time.

Before proceeding just create the database first. Name it as you like and create a table,

Name NVarChar(255) not nullAdderss NVarChar(255) not nullPhone NVarChar(12) not nullAgain named the table as you want. Now its time for coding.
Create a new web page and add a button. We will write code against the button click. Here I am showing you with a DataTable. According to your requirement you put data into the DataTable and proceed.

DataTable dt = newDataTable();

dt.Columns.Add("Name");

dt.Columns.Add("Address");

dt.Columns.Add("Phone");

dt.Rows.Add("Arkadeep", "Kolkata", "123456890");

dt.Rows.Add("Saikat", "Chennai", "99999999");

dt.Rows.Add("Sucheta", "Delhi", "9876543210");

Now we will convert this DataTable into an XML formatted string and will pass that XML file to server. Lets see how?

privatestaticstring ConvertToXML(DataTable dt)

{

DataSet dsBuildSQL = newDataSet();

StringBuilder sbSQL;

StringWriter swSQL;

string XMLformat;

try

{

sbSQL = newStringBuilder();

swSQL = newStringWriter(sbSQL);

dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);

dsBuildSQL.Tables[0].TableName = "DataTable";

foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)

{

col.ColumnMapping = MappingType.Attribute;

}

dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);

XMLformat = sbSQL.ToString();

return XMLformat;

}

catch (Exception sysException)

{

throw sysException;

}

}

Now its time to send that string to database to make entry. We will use a stored procedure to do this.

CREATEPROCEDURE sp_InsertData

(@xmlString VARCHAR(MAX))

AS

BEGIN

DECLARE @xmlHandle INT

DECLARE @stagingTable TABLE

(

[Name] VARCHAR(50),

[Address] VARCHAR(50),

[Phone] VARCHAR(50)

)

EXECsp_xml_preparedocument@xmlHandle output, @xmlString

INSERTINTO @stagingTable

SELECT [Name] ,

[Address],

[Phone]

FROMOPENXML (@xmlHandle,'/DataTable',1)

WITH ([Name] varchar(50)'@Name',

[Address] varchar(50)'@Address',

[Phone] varchar(50)'@Phone'

)

INSERTINTO SampleData([Name], [Address], [Phone])

(SELECT [Name] , [Address],[Phone]FROM @stagingTable)

EXECsp_xml_removedocument@xmlHandle

END

Execute the procedure and save it into your database. And now write the code to execute the stored procedure.