topic RE: Bulk Insert into MySQL Table in Product Forum

Bulk Insert into MySQL Table

JongjongOclari1 — Tue, 22 Dec 2020 06:57:00 GMT

Hi All,

Is there a way to do a bulk insert into MySQL table.

Thanks

------------------------------
Jongjong Oclarit
------------------------------

RE: Bulk Insert into MySQL Table

KristianHolm1 — Tue, 22 Dec 2020 13:58:00 GMT

Hi jongjong

Execute the sql statement and insert rows from a file.

"BULK INSERT

FROM '"&[path filename]&"' WITH (fieldterminator = ' ', rowterminator = '\n')"

------------------------------
Kristian Holm
RPA programmer
PenSam (Pension Company)
Copenhagen Denmark
------------------------------

RE: Bulk Insert into MySQL Table

PabloSarabia — Tue, 22 Dec 2020 14:14:00 GMT

Hello!

I think the best way is to use MySqlBlukLoader. Here is a link of the documentation https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html

Basically, this piece of code:

string connStr = "server=localhost;user=root;database=test;port=3306;password=******"; MySqlConnection conn = new MySqlConnection(connStr); MySqlBulkLoader bl = new MySqlBulkLoader(conn); bl.Local = true; bl.TableName = "Career"; bl.FieldTerminator = "\t"; bl.LineTerminator = "\n"; bl.FileName = "c:/career_data.txt"; conn.Open(); bl.Load();

I hope I helped you

------------------------------
Pablo Sarabia
Programmer
Altamira AM
------------------------------

RE: Bulk Insert into MySQL Table

JongjongOclari1 — Wed, 23 Dec 2020 03:59:00 GMT

Hi Guys,

Thanks for the reply I will try your suggestion once i will get the data from a file.

For now i will use this line of code below as data needs to be validated first.

INSERT INTO projects(name, start_date, end_date)

VALUES ('AI for Marketing','2019-08-01','2019-12-31'), ('ML for Sales','2019-05-15','2019-11-20');

Thanks

------------------------------
Jongjong Oclarit
------------------------------

RE: Bulk Insert into MySQL Table

ewilson — Wed, 23 Dec 2020 19:05:00 GMT

FWIW - There is a MySQL VBO available on the DX.

https://digitalexchange.blueprism.com/dx/entry/9648/solution/blue-prism---mysql-utility

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Bulk Insert into MySQL Table

bassam.yousif — Thu, 24 Dec 2020 09:12:00 GMT

Hi,

This approach will work only if SQL server has access to the file location, in many cases the Bot will get the Bulk Data from somewhere and need to insert it into DB, storing it into the file system may not be an option. also looping records one by one will take ages.

What could be an option here?

------------------------------
Bassam Yousif
Director
DAR-ME
Amman
------------------------------