Enable Bulk Copy Support

[perrysk-msft]

Is your feature request related to a problem? Please describe.

Bulk copy operations (similar to bcp or SqlBulkCopy in .NET) are essential for efficiently inserting large datasets into SQL Server. Supporting this feature in mssql-python would significantly improve performance for data ingestion workflows.
-Performance: Bulk copy reduces the overhead of multiple insert statements, improving data load speed.
-Use Cases: Ideal for ETL processes, large-scale data migrations, and batch inserts.
-Existing Support in Other Drivers: Many database drivers offer bulk insert capabilities, making this a valuable addition.

Describe the solution you'd like

Expose a bulk insert API in mssql-python, allowing users to insert data efficiently.
Example from cTDS: https://zillow.github.io/ctds/bulk_insert.html

[eavanvalkenburg]

Since this is a python driver, please consider not making this a separate api, but just allow and deal with, single, list and AsyncIterable through a single method!

[amachanic]

Since this is a python driver, please consider not making this a separate api, but just allow and deal with, single, list and AsyncIterable through a single method!

You really don't want to put single rows through the bulk copy API - it's much, much slower than an insert in that case. At some point as the row count increases the efficiency of scale improves. I think the correct interface here is Iterable, which will support lists, tuples, generators, etc.

[eavanvalkenburg]

My thinking is that the python side can deal with anything, and then based on the type or the number of records, it decides what to use towards the actual DB

[gustavoleo]

I would love to see this. At least with the same performance of Connector-X (the fastest in the planet today) https://github.com/sfu-db/connector-x

[amachanic]

gustavoleo Correct me if I've missed something regarding Connector-X, but I think the directionality there is the opposite of what we're talking about here: "Bulk Copy" in this context (also called BCP) refers to rapidly copying data into SQL Server by pre-formatting it on the client and using a special protocol that SQL Server exposes for that purpose. See pymssql's bulk_copy function for an example.

[bobduffyie]

This would be a great feature to add. Currently we have to advise customers not to use python for large SQL inserts.

With this feature python becomes a lot more MSSQL friendly.

[amachanic]

Bob Duffy (@bobduffyie) No need to advise your customers like that. There are a handful of other libraries already available that can do bulk copy. I've used by ctds and pytds quite heavily for this and while they're not quite as fast as inserting from C#, either option is orders of magnitude faster than chaining single-row inserts; plenty fast for most use cases.

[leonkosak]

Any ETA on this? I would like to ditch using .bulk_copy() method in pymssql and fully switch to mssql-python.