Hello, you can please illustrate to me how to use Bulk insert with sql, to make a much faster data load to a sql database
Thanks
Hi @Sergio_Andres_Carmona,
Welcome to the Community!
Could you precise the task you would like to do and describe it a little?
Hello, I want to insert data in a bulk SQL database, I am currently using INSERT, but when there is a lot of data the load is slow, I want to know if the load can be made faster
From what I see the Insert activity is working based on DataTable type only so there is not much what can be done here.
I know this response is a bit delayed, but I had the same issues and I couldn’t find a good solution on these forums. I was able to get it working using the following:
- Add the ‘Invoke Code’ Activity
- Add the following Import: System.Data.SqlClient
- Add the following arguments:
ConnectionString - In - String - "DB CONNECTION STRING"
TargetTable - In - String - "TARGET TABLE NAME"
DataToInsert - In - DataTable - DTVARIABLE
- Add the following code:
Using SQLConnection As SqlConnection = New SqlConnection(ConnectionString)
Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConnection)
SQLConnection.Open()
BulkCopy.DestinationTableName = TargetTable
Try
BulkCopy.WriteToServer(DataToInsert)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Using
For any that may come across this - a new Bulk Insert activity was added in v1.5.0 in Oct 2021: https://docs.uipath.com/activities/docs/release-notes-database-activities#v150
@Pablito, An enhancement to that activity to expose the BatchSize arg and allow optional column mapping would be really helpful.
- BatchSize: BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn
- Column mapping example: SqlBulkCopyColumnMapping Class (System.Data.SqlClient) | Microsoft Learn
Thank you for your suggestion @Kristen, I’ve saved it our internal ideas tracker to be considered in the future.
Thank you very much, Maciej!!