If you want to insert large number of data in table with just one shot then you can do it.
While doing my project I came to scenario where I want to send the large number to data to SQL and then insert into tables. I found this very useful. What I did is I passed all records in form of XML in SQL Procedure.

  1. I need to have a data table or data set which contains the whole data to be inserted in to DB.
    1. DataTable dtUsers; //which contains all users in rows
    2. DataSet dsUsers = new DataSet();
    3. dsUsers.Tables.Add(dtUsers);
    4. string strUsersXML = ds.GetXml(); //you will get a string like following,
    6. strUsersXML = '<ROOT><student> < id > 1 < /id> < name > Prashant < /name> < age > 32 < /age> < /student> < student >
    7. < id > 2 < /id> < name > Swami < /name> < age > 42 < /age> < /student> < student >
    8. < id > 3 < /id> < name > Ash < /name> < age > 23 < /age> < /student> < student >
    9. < id > 4 < /id> < name > Kris < /name> < age > 12 < /age> < /student> < student >
    10. < id > 5 < /id> < name > Derek < /name> < age > 75 < /age> < /student> < /ROOT>'
    Below is the example: Open Query analyzer and paste the below code.

  2. Declared the XML Variable first and set the value to it.
    1. CREATE PROCEDURE InsertUsers(@doc nvarchar(max))
    2. as
    3. BEGIN
    4. DECLARE @idoc int
    6. --Create an internal representation of the XML document.
    7. EXEC sp_xml_preparedocument @idoc
    8. OUTPUT, @doc
    9. --Execute a SELECT statement that uses the OPENXML rowset provider.
    10. Insert into Students SELECT id, name, age from
    11. OPENXML(@idoc, '/ROOT/student', 2)
    12. WITH(id int, name varchar(50), age int)
    13. --Select * from @Students
    14. END
    Enjoy.