none
SQL how to add many rows with one statement/procedure RRS feed

  • Question

  • Hi,

    I installed a SQL Server 2005 Express to my computer and I'm developing an application that tries to connect to that a database located in the server. I use SQL Management Studio to create tables etc. In the database there is a table and I am about to add rows to it. Unfortunately I have to add about 3000 rows at one connection to the database. How can I do it? Is there a good solution for it or should update the table 3000 times?
    Wednesday, October 31, 2007 12:59 PM

Answers

  • What I would do is create a stored procedure for inserting a record.  i.e.

     

     

    Code Block

    CREATE Procedure InsertRecord_sp

    @FieldOne varchar(100),

    @FieldTwo varchar(10)

     

    AS

    INSERT INTO MyTable (FieldOne,FieldTwo) values (@FieldOne,@FieldTwo)

     

     

     

    Then, in your code:

     

    Code Block

    SqlConnection cnWork = new SqlConnection(sConString);

    SqlCommand cmdWork = new SqlCommand("exec InsertRecord_sp @FieldOne,@FieldTwo");

     

    cmdWork.Parameters.AddWithValue("@FieldOne", var1);

    cmdWork.Parameters.AddWithValue("@FieldTwo", var2);

     

    cnWork.Open();

    cmdWork.Connection = cnWork;

    cmdWork.ExecuteNonQuery();

     

     

    (The code block assumes you have a variable for your connection string)

     

     

    Wednesday, October 31, 2007 1:44 PM

All replies

  • Is this a one time thing?  Where is the data coming from?

     

    If it's a one time import from an external data source (excel spreadsheet, text file, etc.) just use the import wizard (go to your database, right click, Tasks->import data).

     

    If it's data that already exists in your database but in other tables, build a query to return the data you want, then use a SELECT INTO query.

     

    If it's data you need to create dynamically, you could either whip together an application to import it or build a script with the relevant insert statements.

     

    -Bob

    Wednesday, October 31, 2007 1:19 PM
  • No, it is not a one time thing, it happens occasionally. My program creates about 3000 data lines and I'd like to insert them into the database once in a week. I am not good with procedures so I need help how to do it. What do you mean by building a script to do it? You mean a procedure with parameters and it contains a while loop that runs until the no other input? Unfortunatelly I am bad at SQL procedures, never used them.
    Wednesday, October 31, 2007 1:33 PM
  • What I would do is create a stored procedure for inserting a record.  i.e.

     

     

    Code Block

    CREATE Procedure InsertRecord_sp

    @FieldOne varchar(100),

    @FieldTwo varchar(10)

     

    AS

    INSERT INTO MyTable (FieldOne,FieldTwo) values (@FieldOne,@FieldTwo)

     

     

     

    Then, in your code:

     

    Code Block

    SqlConnection cnWork = new SqlConnection(sConString);

    SqlCommand cmdWork = new SqlCommand("exec InsertRecord_sp @FieldOne,@FieldTwo");

     

    cmdWork.Parameters.AddWithValue("@FieldOne", var1);

    cmdWork.Parameters.AddWithValue("@FieldTwo", var2);

     

    cnWork.Open();

    cmdWork.Connection = cnWork;

    cmdWork.ExecuteNonQuery();

     

     

    (The code block assumes you have a variable for your connection string)

     

     

    Wednesday, October 31, 2007 1:44 PM
  • And this induces that in my program code I have to set the variables and call the procedure in a for loop 3000 times, right?
    Wednesday, October 31, 2007 1:51 PM
  • Yep, just toss that in the loop - and you would need to populate the variables as well.

     

    Wednesday, October 31, 2007 1:56 PM
  • Thanks the code, it works like dream.
    Wednesday, October 31, 2007 2:48 PM