Understanding the bulk insert option

Bulk insert is an optional flag for conn:merge{} that can be used with Microsoft SQL Server and MySQL.

It ignores the key columns defined in the VMD files and always does an INSERT using the bulk insert syntax. If your table has a large number of rows, this can improve performance.

Before You Start

How It Works

Using bulk insert in Iguana is very simple you just need to set bulk_insert = true.

Here is an example of a call to conn:merge{} that uses the bulk insert option:

Because bulk insert only does inserts you will get an error if you try to merge a duplicate record:

Sample Code

To use this code in your script, copy and paste the following code sample:

local conn = db.connect{
   api=db.SQL_SERVER, 
   name='Test', 
   user='root', 
   password='password', 
   live = true
}

function main(Data) 
   local Msg, Name = hl7.parse{vmd='demo.vmd', data=Data}
   local T = db.tables{vmd='demo.vmd',name=Name}

   T = mapPatient(Msg.PID, T)

   conn:merge{data=T, bulk_insert = true, live=true}
end

-- We map Mrn to the Patient ID Number
function mapPatient(PID, t)
   t.patient[1].Id            = PID[3][1][1]
   t.patient[1].LastName      = PID[5][1][1][1]
   t.patient[1].GivenName     = PID[5][1][1][1]
   t.patient[1].Race          = PID[10][1][1]
   t.patient[1].PhoneHome     = PID[13][1][1]
   t.patient[1].PhoneBusiness = PID[14][1][1]
   t.patient[1].Religion      = PID[17][2]
   t.patient[1].MaritalStatus = PID[16][2]
   t.patient[1].Ssn           = PID[19]
   t.patient[1].LicenseNumber = PID[20][1]
   return t
end

What’s Next?

We showed you the the bulk insert option for the conn:merge{}. If you want more control it is possible use native SQL bulk inserts statements with conn:execute{}.

You might want to use a trigger during bulk insert operations to prevent invalid inserts (i.e., check on insert if record exists and do an update instead). You can also use triggers for other data validation etc.

If you want triggers to fire during bulk insert you will need to do the following:

• For MySQL triggers fire by default so no action is needed
• For MSSQL you need to use conn:execute{} to run a native bulk insert statement, using the FIRE_TRIGGERS option

If you do not want triggers to fire during bulk insert you will need to do the following:

• For MSSQL triggers do not fire by default so no action is needed
• For MySQL you can use a variable in the trigger to prevent it from running
  Note: I cleaned up the code from this discussion:
  • Trigger code:

    IF @enable_triggers = 1 THEN
        // Do something useful when the trigger is enabled
    END IF;

  • Iguana script:

       conn:execute{sql='SET @enable_trigger = 0', live=true} -- disable triggers
       conn:merge{data=T, bulk_insert=true, live=true}
       conn:execute{sql='SET @enable_trigger = 1', live=true} -- re-enable triggers

     

    1. Set the variable to disable triggers using conn:execute{sql = 'SET @enable_trigger = 0'}
    2. Run conn:merge{}
    3. Set the variable to enable triggers using conn:execute{sql = 'SET @enable_trigger = 1'}