Is this the fastest way to add multiple rows to a SQL Server table

Question

I have a general question that I have not found any answer to. I think it is important to ask this question so I know how to structure the whole application so I am on the right track from the very beginning.

I believe it is practical/efficient to add Multiple rows in one Query command. The below code adds 3 rows with 5 values in 5 columns at once.

See this image also of the table:

Image of DataTable

My question now is. This table will later have 100,000 columns where I am thinking of for example adding 10 rows at a time. Now imagine that this cmdString will be EXTREMELY long.

My question simply is. Is this okay or is this the wrong way to go here when I add so much information in one query? (Is this the fastest way to do it or should I do the below code in another way?)

Thank you!

void addMultipleRowWithValuesSQL()
{
    String cmdString = "INSERT INTO DateTimes(DateTime,F1,F2,G1,G2) " + 
                       "VALUES" +
                           "('201005011715','1',2,'3','4')," +
                           "('201005011730','5',6,'7','8')," +
                           "('201005011745','9',10,'11','12');";
 
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
    {
        using (SqlCommand comm = new SqlCommand(cmdString))
        {
            try
            {
                comm.Connection = conn;

                conn.Open();
                int i = comm.ExecuteNonQuery();

                if (i != 0) 
                {
                    MessageBox.Show(i + "Rows Added"); 
                }
            }
            catch (SqlException ex) 
            {
                 MessageBox.Show(ex.ToString()); 
            }
        }
    }
}

static private string GetConnectionString()
{
    return "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\andre\\source\\repos\\TestDatabaseCreation\\DatabaseTest.mdf;Integrated Security=True;Connect Timeout=30";
}

Having 100000 columns is definitely wrong way and many systems would not even accept that. You should redesign the schema. To the question, you can use some batch insert for fast insertion, make the server parse through piles of text is not good idea. — Antonín Lejsek
– Antonín Lejsek, Commented Oct 3, 2020 at 17:54
@AntonínLejsek Based on the picture it's SQL Server, which supports only 1024 columns, ok, up to 30000 in a sparse table :-) — dnoeth
– dnoeth, Commented Oct 3, 2020 at 18:06
Does SQL only support 1024 columns. What is a sparse table. Should I then use that if 80% of columns will have a float value and 20% a bool value? If I understand, I must use many tables which I will sync somehow with all those 100,000 columns? — coding
– coding, Commented Oct 3, 2020 at 18:15
@Antonin yes, this was what I was afraid of. So batch insert is the way to go. I am not familiar of how to do that, but perheps there is an example somewhere then. I thought about that also, that it has to be enormous amount of parsing with such a long string. — coding
– coding, Commented Oct 3, 2020 at 18:18
I have trouble to find any good example where I can understand how I can make a batch insert of my code example. It would be very useful to see how that could be done? The speed improvement seems to be very big using that. — coding
– coding, Commented Oct 3, 2020 at 19:12

Usama Safi · Accepted Answer · 2020-10-03 20:55:30Z

2

They call it Bulk Insert, this link here will get you to a nice example that can explain. Bulk Insert In SQL Server From C#

answered Oct 3, 2020 at 20:55

Usama Safi

1718 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coding Over a year ago

Usama, Thank you for this solution.

coding · Accepted Answer · 2020-10-04 07:24:26Z

1

I was looking at this link that also showed of how to add multiple rows in one transaction. https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching

My question is if this is a valid and good way to do batch insert?
Or does this code also involve enormous amount of parsing like in my original post, if I would add for example 10,000 rows with VALUES for example 200 columns in one transaction?

Just to have a confirmation on that. The code do work to mention.

    void addMultipleRowWithValuesSQL()
    {
        List<string> dbOperations = new List<string>();
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011800', '11', '22', '33', '44');");
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011815', '55', '66', '77', '88');");
        dbOperations.Add("INSERT INTO DateTimes(DateTime, F1, F2, G1, G2) VALUES('201005011830', '99', '100', '101', '102');");

        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            conn.Open();
            SqlTransaction transaction = conn.BeginTransaction();

            foreach (string commandString in dbOperations)
            {
                SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
        }
    }

edited Oct 4, 2020 at 7:24

answered Oct 4, 2020 at 7:11

coding

632 silver badges11 bronze badges

8 Comments

Usama Safi Over a year ago

It work, but it's not efficient if the amount of inserted rows are unknown, can be hundreds, but for few insertion you can even make it simpler, using StringBuilder.

Usama Safi Over a year ago

System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("your insert statement, must be ended with semicolon"); sb.Append("another insert statement, must be ended with semicolon"); sb.Append("and another insert statement, must be ended with semicolon"); then you just use this sb in your command to be executed like SqlCommand cmd = new SqlCommand(sb.ToString(), conn, transaction); without the need for that foreach.

coding Over a year ago

Thank you, yes I am familiar with the StringBuilder as it is very fast. But I wonder there, even if I use StringBuilder, shouldn't it be the same problem that the SQL needs to parse all this if the amount is enormous like 10,000 rows. I might wonder if your solution is better in your answer perheps or will the StringBuilder solution be as efficient as your bulk insert/datatable solution?

Usama Safi Over a year ago

Definitely bad to go the List<string> Way and StringBuilder way for anything beyond say 10 simple insert statement, some DBMSs will return an exception if it get too long receiving that much of sql statements.

coding Over a year ago

Usama, that is so great. Thank you for this solution. I will implement the Bulk Insert then. I will mark your post as answer also.

|

Collectives™ on Stack Overflow

Is this the fastest way to add multiple rows to a SQL Server table

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related