Hi, I've been working the past couple of days to import a bunch of csv data into my system.
I have a temp_holding table which basically just matches the csv format. I have a trigger before insert on each row on the temp_holding table, below. The problem is that inserting into temp_holding takes more than a SECOND PER ROW! What can I do to speed this up, because importing 106 records is going to take a day and a half??? Kind regards, John Hughes CREATE FUNCTION data_import() RETURNS "trigger" AS ' DECLARE User RECORD; check RECORD; BEGIN SELECT INTO check * FROM core_contacts WHERE primaryphone = NEW.number; -- Checks for duplicate entries IF NOT FOUND THEN SELECT INTO User id FROM core_users WHERE username = NEW.username; IF User.id IS NULL THEN User.id := 37; END IF; INSERT INTO core_contacts ( primaryphone, zip, regionid, city, address, firstname, lastname) VALUES ( NEW.number, NEW.zip, (SELECT id FROM core_regions WHERE name = NEW.state), NEW.city, NEW.street, NEW.first_name, NEW.last_name); INSERT INTO core_leads (contactid, leadstatustypeid, createdbyuserid, leadtypeid, notes) VALUES ( max(core_contacts.id), 1, User.id, 1, NEW.agent_comments ); IF NEW.lead_date = '''' THEN NEW.lead_date := ''now''; END IF; INSERT INTO core_leadevents ( leadid, leadeventtypeid, userid, created, notes ) VALUES ( max(core_leads.id), 4, User.id, date(NEW.lead_date), ''Imported on ''+''now'' ); INSERT INTO core_leadattributes (value, attributetypeid, leadid) VALUES ( NEW.loan_amount, 18, max(core_leads.id)); INSERT INTO core_leadattributes (value, attributetypeid, leadid) VALUES ( NEW.property_value, 1, max(core_leads.id)); INSERT INTO core_leadattributes (value, attributetypeid, leadid) VALUES ( NEW.interest_rate, 14, max(core_leads.id)); INSERT INTO core_leadattributes (value, attributetypeid, leadid) VALUES ( NEW.interest_type, 13, max(core_leads.id)); ELSE NEW.duplicate = true; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly