Re: No duplicate records, ignore
Ya know, I didn't ask for help with INSERT IGNORE or ON DUPLICATE KEY specifically. I asked for help avoiding duplicates. I already came up with a solution using python to execute what I wanted successfully. Pretty sure, however, this is more effecient:
python calls stored procedure with list and other variables. Stored procedure creates temp table, then inserts with join.
new.py:
# create stored procedure code
sql_string = "call spnew('%s','%s',\"%s\",%s,'%s','%s');" % (
exch,
call,
', '.join(columns).lower(),
values_str.lower(),
curs,
curt
)
spnew.sql:
USE mydb;
DROP PROCEDURE IF EXISTS spnew;
DELIMITER //
CREATE PROCEDURE spnew (exch VARCHAR(10),calltype VARCHAR(50),pkeys TEXT,prows TEXT,base VARCHAR(10),pair VARCHAR(10))
BEGIN
DECLARE strLen INT DEFAULT 0;
DECLARE SubStrLen INT DEFAULT 0;
DECLARE tableName VARCHAR(50) DEFAULT CONCAT('ex',calltype);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
-- create temporary table
CREATE TEMPORARY TABLE exnewtemp LIKE tableName;
-- begin loop for insert
insert_data: LOOP
SET strLen = CHAR_LENGTH(prows);
-- insert row into temp table
INSERT INTO exnewtemp (pkeys,exid) VALUES (SUBSTRING_INDEX(prows, ',', 1),(SELECT et.exid FROM exs et WHERE et.exabbr = exch),(SELECT curid FROM curs WHERE abbr = base),(SELECT curid FROM curs WHERE abbr = pair));
SET SubStrLen = CHAR_LENGTH(SUBSTRING_INDEX(prows, ',', 1))+2;
-- reduce list by used item
SET prows = MID(prows, SubStrLen, strLen);
IF prows = '' THEN
LEAVE insert_data;
END IF;
END LOOP insert_data;
-- insert new, unique records in perm table
INSERT INTO tableName
SELECT n.* FROM exnewtemp n
LEFT JOIN tableName e on (n.exid = e.exid AND n.at = e.at AND n.bid = e.bid and n.p = e.p and n.pid = e.pid)
WHERE e.id IS NULL;
COMMIT;
END //
DELIMITER ;