Updating multiple rows in sql server 2016
To investigate the performance difference between the single row insert and multiple rows insert, I created a program which dynamically formed an statement, which loaded one million rows in 9 seconds.
The sizes of other file types were 30 times smaller, and we successfully used Informatica Power Center 9 to parse, validate, transform, and load the files into a staging table in SQL Server.
We engaged two parallel processes and it consumed about 60% of the server resources, so users could still load files of other types at the same time.
I was intrigued by the multiple rows insert method of loading data, and wondered whether I could have improved the performance by doing it a different way. The database and the client applications were placed on the same personal computer (Lenovo Think Centre M90, Windows XP).
statements, use a parameterized prepared statement from the client, or call a stored procedure with the data passed as a parameter.
From that stored procedure, you’ll probably want to insert the data into a table by using statements that insert several rows at a time.