how to Update sql table from collection data in one go?

[harish17]

Hi,

I have a collection which has 500 rows and want to update this data in to a table in sql . I was aware I can update row by row by looping each one is there a way I can update all the data in the collection in one go from Blue prism Process to sql table?

If it is calling

Thanks

 

[SaJ]

hi,

You can achieve this by OLEDB.
Copy the collection values into excel, create the connection string to consider excel as database and sheet as table. Then execute the query.

Thanks,
SaJ

 

[harish17]

Thank you sahela.

 

[Adrian]

@SaJ,

Do you know if a collection with multiple rows and columns can be written to an excel file via OLEDB?

Thanks,
Adrian

 

[SaJ]

Hi Adrian,

We can achieve via OLEDB insert/update query. But we need to loop through the collection.

If you want to insert into single shot, then use write collection from MS excel VBO

Thanks,
SaJ

 

[Dhriti]

hi,

You can achieve this by OLEDB.
Copy the collection values into excel, create the connection string to consider excel as database and sheet as table. Then execute the query.

Thanks,
SaJ

i have done all of the avove but its still not viewing the answers

 

[ashibm]

Can you provide more detail on how to use Excel to import data into sql

 

[sheebanancy90]

CAn someone give code for OLEDB insert/update query

 

[lakshmis]

hi,

You can achieve this by OLEDB.
Copy the collection values into excel, create the connection string to consider excel as database and sheet as table. Then execute the query.

Thanks,
SaJ

Hi, Could you please provide the syntax on how to copy the whole collection data to excel sheet in one go. I am able to get one row at a time to excel sheet not the whole collection data. Please suggest.

 

[sahil_raina_91]

Hi, Could you please provide the syntax on how to copy the whole collection data to excel sheet in one go. I am able to get one row at a time to excel sheet not the whole collection data. Please suggest.

AFAIK, I dont think Oledb allows multiple DML statements in a single query.
So what you are looking for is not possible via Oledb in a single query.

 

[lakshmis]

Okay, thank you Sahil for your reply.