I am inserting on the order of 100k rows to a remote SQL server. Each row is a couple dozen columns but I am only sending an small string account number from a text file to one column using SQLCommand only. IE No ORM or DataTables. I created a function to do this locally with ExecuteNonQuery and it was acceptable. But in testing on the remote server it managed only 6.7 rows per second which is unacceptably slow. The user could literally be there all day in some cases. Can you suggest a better method or alternative ideas?

I was considering BulkInsert as it seems the smartest but at this point I don't know if I will be able to access the remote file system. If I do it will have to be via FTP and even if the current server allows FTP access I'm not sure the next one will. But it's possible I don't fully understand how this works.

Is there any way to send all of the data to the remote system and parse it there?

I was looking at ExecuteAsync. I've never done anything like this before so I don't know if it's viable and I will have to invest significant time educating myself before I can make that decision. But if someone can tell me this will solve my problem I'll gladly bone up on it.

A final note: My SQL command contains and "IF EXISTS" to prevent inserting a duplicate account number. Maybe this is a bad idea? It seemed like a good idea to let the SQL Server make the decision. I could instead get a list of account numbers first and make the decision on my end before sending the command. Under normal circumstances there should not be any rows in the table that match a criteria which defines this set.