INSERT more than 1000 rows into SQL Server database from JSON in C# code

Question

I'm getting an error saying:

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

I'm getting JSON-data from an API-call. And when I'm trying to insert this in my SQL Server database I'm getting the error. I'm trying to insert 3000 rows. How can I solve this issue? The call result from the APi is stored in the variable "body", and then deserialized into the variable "json".

This is my code:

using (var response = await client.SendAsync(request))
{
    response.EnsureSuccessStatusCode();
    var body = await response.Content.ReadAsStringAsync();

    var json = JsonConvert.DeserializeObject<Root>(body);
    var Api = json.api;

    string SqlString = "INSERT INTO land.leagues(" +
                                                "league_id" +
                                                ",name" +
                                                ",type" +
                                                ",country" +
                                                ",country_code" +
                                                ",season" +
                                                ",season_start" +
                                                ",season_end" +
                                                ",logo" +
                                                ",flag" +
                                                ",standings" +
                                                ",is_current" +
                                                ",coverage_standings" +
                                                ",coverage_players" +
                                                ",coverage_topScorers" +
                                                ",coverage_predictions" +
                                                ",coverage_odds" +
                                                ",coverage_fixtures_events" +
                                                ",coverage_fixtures_lineups" +
                                                ",coverage_fixtures_statistics" +
                                                ",coverage_fixtures_playersStatistics" +
                                                ",created" +
                                                ") VALUES"; 


    foreach (var a in Api.leagues)
    {
        SqlString += "(" +
                                 "'" + a.league_id +
                                 "','" + a.name.Replace("'","`") + 
                                 "','" + a.type +
                                 "','" + a.country +
                                 "','" + a.country_code +
                                 "','" + a.season +
                                 "','" + a.season_start +
                                 "','" + a.season_end +
                                 "','" + a.logo +
                                 "','" + a.flag +
                                 "','" + a.standings +
                                 "','" + a.is_current +
                                 "','" + a.coverage.standings +
                                 "','" + a.coverage.players +
                                 "','" + a.coverage.topScorers +
                                 "','" + a.coverage.predictions +
                                 "','" + a.coverage.odds +
                                 "','" + a.coverage.fixtures.events +
                                 "','" + a.coverage.fixtures.lineups +
                                 "','" + a.coverage.fixtures.statistics +
                                 "','" + a.coverage.fixtures.players_statistics +
                                 "','" + DateTime.Now +
                                 "'),";
    }

    SqlConnection con = new SqlConnection(@"_ConnectionString_");
    SqlCommand cmd;

    con.Open();
    cmd = new SqlCommand("TRUNCATE TABLE land.leagues " + SqlString.Remove(SqlString.Length - 1), con);

    cmd.ExecuteNonQuery();    
}

These are all my classes:

public class Fixtures
{
    public bool events { get; set; }
    public bool lineups { get; set; }
    public bool statistics { get; set; }
    public bool players_statistics { get; set; }
}

public class Coverage
{
    public bool standings { get; set; }
    public Fixtures fixtures { get; set; }
    public bool players { get; set; }
    public bool topScorers { get; set; }
    public bool predictions { get; set; }
    public bool odds { get; set; }
}

public class League
{
    public int league_id { get; set; }
    public string name { get; set; }
    public string type { get; set; }
    public string country { get; set; }
    public string country_code { get; set; }
    public int season { get; set; }
    public string season_start { get; set; }
    public string season_end { get; set; }
    public string logo { get; set; }
    public string flag { get; set; }
    public int standings { get; set; }
    public int is_current { get; set; }
    public Coverage coverage { get; set; }
}

public class Api
{
    public int results { get; set; }
    public List<League> leagues { get; set; }
}

public class Root
{
    public Api api { get; set; }
}

you'll have to batch it in queries of 1000 at a time. Or better, use a bulk import process. — ADyson, Oct 28 '20 at 15:27
`MySQL` or `MSSQL` ? on `MSSQL` you need to use bulk insert to surpass the 1000 limit insert / update in a single query. Although this is not recommended to insert that many records this way, Import should be prefered. — Franck, Oct 28 '20 at 15:28
Don't forget to wrap the thing into transaction if you will go with "split into batches" approach. — Evk, Oct 28 '20 at 15:48
Don't trust the JSON, protect against SQL Injection - see https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement. (This will also remove the need for your a.name.Replace("'","`") — Alex K., Oct 28 '20 at 16:00
I found out that i didn't have "bulkadmin" privelgies on my SQL Server. But inserting all my rows 1 at a time works. I will look into the issue regarding SQL injection, thank you for the input — Opston, Oct 29 '20 at 09:47

score 0 · Answer 1 · answered Oct 28 '20 at 15:28

0

The maximum number of rows you can insert that way is 1000 so the error is fair enough. I would simply divide my rows into thousands and make a new INSERT statement for each batch of rows. Hope that makes sense :)

answered Oct 28 '20 at 15:28

Rasmus

55
1
7

score 0 · Accepted Answer · answered Oct 28 '20 at 18:49

You should insert each row one by one.

https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/

Gist - 23 columns means inserting more than 2 at a time isn't worth it. Time wise, single row inserts is almost as fast as multiple row inserts. There is a case where stringifying your parameters can give you a 50% boost, but IMO not worth it. If you reduce your columns to 2 or even 7 it might be worth it.

Take those estimates with a grain of salt - the performance of the box may affect the relative benefits.

Of course keeping your row inserts to one at a time makes it easy to parameterize your query and keep things clear while eliminating the potential of sql injection. Not having to compile the sql everytime, while doing most of the parameter work on the client can help a lot also.

Yes that works. I just thought it wasn't "best practice" to that. — Opston, Oct 29 '20 at 09:44
I used to think that too, which is why I was expansive with my comment. Different cases for different things - but I've never gone back to 'optimize' my sql to multiple row inserts; it was never worth it. — Gerard ONeill, Oct 29 '20 at 15:46

INSERT more than 1000 rows into SQL Server database from JSON in C# code

2 Answers2