Database Fundamentals

Understanding the bulk insert option

Contents

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.

Note: Bulk insert does inserts only, if you need to do updates you must handle these separately.

Before You Start

Warning! You should be aware that the behaviour of triggers for bulk insert for MSSQL is different from MySQL:

  • Triggers do not fire in MSSQL (this is to improve performance of bulk insert operation)
  • Triggers do fire in MySQL (there is no command to enable/disable triggers in MySQL)

Note: See the “What’s Next?” section at the bottom of the page for information on using triggers with bulk insert.

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:

Note: If you were using conn:merge{} without the bulk insert option the corresponding record would be updated, and this error would not occur.

 

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:

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'}
Continue: Calling a SQL stored procedure