2

My original purpose was to bulk insert into my db I tried pyodbc, sqlalchemy and ceodbc to do this with executemany function but my dba checked and they execute each row individually.

his solution was to run procedure that recieve table (user defined data type) as parameter and load it into the real table.

The problem is no library seem to support that (or at least no example on the internet).

So my question is anyone try bulk insert before or know how to pass user data defined type to stored procedure?

EDIT

I also tried bulk insert query but the problem it requires local path or share and it will not happend because organizition limits

Oren Haliva
  • 351
  • 3
  • 14
  • I have seen people create a stored procedure, which takes a XML parameter that represents multiple rows. The procedure inserts data from that XML into a temporary table, and then makes UPDATE (with JOIN) and INSERT. As a result you get one procedure call which can bulk insert/update. – van Aug 31 '15 at 05:59
  • Are the preformance the same? – Oren Haliva Aug 31 '15 at 06:18
  • Compared to what exactly? I think that if you really want just one procedure (and one DB call), you can go the route with XML (or any other way encoded) table. In any case: are you trying to get better performance, or just execute them in one transaction? Also take a look at [Bulk Operations](http://docs.sqlalchemy.org/en/rel_1_0/orm/persistence_techniques.html#bulk-operations) documentation page of sqlalchemy, especially on `executemany`. – van Aug 31 '15 at 06:42
  • Possible duplicate of [How to call stored procedure with SQLAlchemy that requires a user-defined-type Table parameter](https://stackoverflow.com/questions/50141058/how-to-call-stored-procedure-with-sqlalchemy-that-requires-a-user-defined-type-t) – Ilja Everilä May 03 '18 at 20:31

1 Answers1

0

Sqlalchemy bulk operations doesn't really inserts a bulk. It's written in the docs. And we've checked it with our dba. Thank you we'll try the xml.

wa11a
  • 183
  • 1
  • 7