autoincrement while doing the insert
Here is my CREATE that seems to work:
$sql = "CREATE TEMPORARY TABLE BC_PR_list_temp(
id INT AUTO_INCREMENT primary key NOT NULL,
taxyear VARCHAR(10),
employee_email VARCHAR(80),
employee VARCHAR(80),
qualified_dollars int NOT NULL DEFAULT 0,
title varchar(80),
role varchar(80),
num_BC int NOT NULL DEFAULT 0,
num_PR int NOT NULL DEFAULT 0,
tipe varchar(4),
item varchar(80),
dollarsPerBC int NOT NULL DEFAULT 0,
dollarsPerPR int NOT NULL DEFAULT 0,
business_component_string varchar(120),
project_string varchar(120),
CONSTRAINT ONLYONE UNIQUE(employee_email, item, qualified_dollars)
)";
$iRc=$this->db->query($sql);
And here is my insert which yields an error Duplicate entry 1735 for key BC_PR_list_temp.PRIMARY. How can I auto increment the id while doing the insert?
$sql = "INSERT IGNORE INTO BC_PR_list_temp SELECT DISTINCT srat.id, srat.taxyear , srat.employee_email , srat.employee , srat.qualified_dollars
,e.employee_title as title,e.role as role,e.num_BC ,e.num_PR,'BC','',srat.qualified_dollars/e.num_BC as dollarsPerBC,
srat.qualified_dollars/e.num_PR as dollarsPerPR, e.business_component_string,e.project_string
FROM survey_results_activities_temp srat, employees e
WHERE campaign= ? AND email= ? AND e.employee_email=srat.employee_email AND srat.employee_email IN (SELECT srat.employee_email FROM employees WHERE FIND_IN_SET(?, e.business_component_string))";
$query2 = $this->db->query($sql, array($campaign, $email, $BC));