DBBulkCopy

DBBulkCopy allows you to efficiently copy a large number of rows to a table in a SQL Server or Oracle database.

This function creates a Loader object in its execution path. Use the SetValue function to add the rows that you want to insert. See the example below.

Properties

Connection type

The type of database driver to use to connect to the database.

The supported driver types:

• SQL Server

• Oracle

Connection string

The connection string that specifies how to connect to the database.

Timeout

The timeout value in seconds. 0 indicates the bulk copy operation will wait indefinitely.

Table

The name of the destination table.

Click on the ... icon to open the Editor.

Batch size

The number of rows to collect in a transaction before it is committed to the server.

Example

Suppose you have a CSV text file with many records that need to be loaded into a database table. Using the DbBulkCopy function as shown below will run much quicker than doing it using the more generic ExecuteSQL function.

Steps:

1. From the Database plugin, drag the DBBulkCopy function onto the design canvas.
2. Set the DBBulkCopy properties (see above for details).
3. From the File plugin, drag the TextFileRead function onto the design canvas (nested below your DBBulkCopy function).
4. Set the TextFileRead properties to read all the rows from the source file that you want to copy across to your destination database.
5. From the Linx plugin, drag the SetValue function onto the design canvas (nested below the TextFileRead function).
   
   
6. Set the Target property of the SetValue function to DbBulkCopy.Loader.Write.
   
   
7. Then click the Set Fields editor button next to the Source property to assign the values to the table's columns.
   

BulkCopy from one DB to another