SqlBulkEngine

Overview

The SqlBulkEngine allows a power user to easily map text file data into multiple tables. It works as follows:

• • Creates (or updates) a staging table to match the columns present in a text file

  • Bulk copies text data to a staging table

  • Executes a statement to process data staging table

    • this leverages ImportFileMap.Destination to offer some nifty sugar when designing statements; see below

There a several options (placed in ImportFileTemplate.Parameters) one can use to configure the SqlBulkEngine, including:

• • Truncate: (optional, defaults to false) if true, the staging table will be truncated

  • TableName: (optional, defaults to file name) name of the staging table to bulk copy data into

  • TargetTableName: (optional, defaults to null) name of a target table to ensure each text data column exists in

  • HeaderRow: (optional, defaults to true) whether the text file contains a header row

  • Delimiter: (optional, defaults to ',') the delimited used in the text file

  • CharSet: (optional, defaults to 'OEM') character set used by the text file

  • InferSchema: (optional, defaults to 'NoMapping')

    • NoMapping: Import File Map rows are created if there are none

    • Always: Import File Map rows are always created (if not present)

    • Never: Import File Map rows are never created automatically

Statement Processing

When configuring a statement, several SQL substitution patterns are available:

• • {Import.StagingTable} => name of the staging table

  • {Import.TargetTable} => name of the target table

  • {Import.AllColumns} => emit a list of staging table columns

  • {Import.Select.*} => emit select clause for columns that have a destination beginning with *

    • this emits the staging table column names

  • {Import.Insert.*} => emit insert clause for columns that have a destination beginning with *

    • this emits the destination table column names

  • {Import.Update.*} => emit update clause for columns that have a destination beginning with *

    • this emits {DestinationColumn} = {StagingColumn}

  • {Import.Foreach.*}...{Import.Endeach.*} => for i=0..10, repeat clause for columns that have a destination beginning with *, and the source contains i

    • {Import.X} => i (iteration) during a for each loop

    • this is currently limited to a maximum of 10 iterations

Assume a client provides a text file containing the following columns, and a power user has defined a destination mapping in the associated ImportFileMap rows:

• • Property_Address: maps to Property.Address

  • Property_City: maps to Property.City

  • Account_Number: maps to Loan.Loan

  • Account_Balance: maps to Loan.UPBAmount

  • Borrower1_First: maps to Contact.FirstName

  • Borrower1_Last: maps to Contact.LastName

  • Borrower2_First: maps to Contact.FirstName

  • Borrower2_Last: maps to Contact.LastName

  • Borrower3_First: maps to Contact.FirstName

  • Borrower3_Last: maps to Contact.LastName

The following statement:

INSERT INTO Property ({Import.Insert.Property}) SELECT {Import.Select.Property} FROM {Import.StagingTable}

INSERT INTO Loan ({Import.Insert.Loan}) SELECT {Import.Select.Loan} FROM {Import.StagingTable}

{Import.Foreach.Borrower}

INSERT INTO Borrower ({Import.Insert.Borrower}, Sequence) SELECT {Import.Select.Borrower}, {Import.X} FROM {Import.StagingTable}

{Import.Endeach.Borrower}

will emit (assuming the staging table name is 'MyStagingTable'):

INSERT INTO Property (Address, City) SELECT Property_Address, Property_City FROM MyStagingTable

INSERT INTO Loan (Loan, UPBAmount) SELECT Account_Number, Account_Balance FROM MyStagingTable

INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower1_First, Borrower1_Last, 1 FROM MyStagingTable

INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower2_First, Borrower2_Last, 2 FROM MyStagingTable

INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower3_First, Borrower3_Last, 3 FROM MyStagingTable

In the future, if the text file is extended to include:

• Account_Status: maps to Loan.Status

• Property_Zip: maps to Property.PostalCode

• Borrower4_*: maps to (same contact mappings)

  • Borrower5_*: maps to (same contact mappings)

no changes to the statement will need to be made.

Data Translation

Some columns may need values translated between the staging an destination table. From the example above:

• Assume Loan.Status need to map 'A' to 'Active', 'I' to 'Inactive'

• Assume Loan.Loan should be 10 characters long, but Account_Number truncates leading zeros

Such translations should be handled in the statement like this:

-- Translate data before copying it elsewhere

UPDATE {Import.StagingTable} SET

  Account_Status = CASE Account_Status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE Account_Status END,

  Account_Number = REPLACE(STR(Account_Number, 10), SPACE(1), '0')

-- Okay, now copy elsewhere

INSERT INTO Property ({Import.Insert.Property}) SELECT {Import.Select.Property} FROM {Import.StagingTable}

INSERT INTO Loan ({Import.Insert.Loan}) SELECT {Import.Select.Loan} FROM {Import.StagingTable}

{Import.Foreach.Borrower}

INSERT INTO Borrower ({Import.Insert.Borrower}, Sequence) SELECT {Import.Select.Borrower}, {Import.X} FROM {Import.StagingTable}

{Import.Endeach.Borrower}