2 Replies Latest reply on Jul 17, 2006 4:06 AM by elDonrico

    Batch Insert in db

    elDonrico Level 1
      i have this script i have been tring to do, but not sure how the 'loop' would work, if anyone could help me please...

      say i have 3 tables, for ease i will call them employee, assignment, pay
      employee - empid, firstname, lastname, ssn
      assignment - assid, payrate
      pay - serialID, Batchid, empid, assid, payamount

      ok, lets say i have a table (clocks) that has ssn, assid, hoursworked.
      it has 1000 records in it. what i want to do is take that clocks table, match the ssn with the employee table to get the empid, match the assid with assignment to get payrate, take the hours times the payrate to get the payamount, and insert that line in the pay table... 'loop' through this until the end... but, one last catch... the batchid is say 200, but, there can only be 100 records per batch, so it must stop at 100 and change the batchID to 201, do another 100 records, change batch to 202 etc...

      everything i have tried so far has been a very pathetic attempt. in all honesty this is a clock csv file, which i plan to use coldfusion to dump it into a timesheet table in an informix database. the pay table actually has 85 different fields,. i am confident that once i get this basic loop action done, i can accomplish the rest of this script... any help please?