May 30, 2006 at 2:13 pm
I am trying to insert records from a table into second table. My original attempt worked except I would end up with duplicate entries. I then added some primary keys, but then the query would bomb when the duplications occured.
The next thing I tried is an If Not Exists, but that errored out as well. Does anyone have any suggestions? Here is the last query I was working with.
And this does not need to be super efficent as it runs twice a day on a few reasonably small tables.
-- add records if they already do not exist
IF NOT EXISTS(
select Operator_ID, Operator_Name, Email_Address, Master_Job, Company_Code
FROM AP_Job_Cost_Security22
)
insert into AP_Job_Cost_Security22 (Operator_ID, Operator_Name, Email_Address, Master_Job, Company_Code)
select distinct O.Operator_ID,O.Operator_Name, O.Email_Address, U.Job_number, U.Company_Code
from JC_JOB_USER_FIELDS_DET_MC U
inner join pa_operator_master O on U.alpha_Field = O.Operator_Name
WHERE
ltrim(rtrim(O.Email_Address)) <> ''
job_number not like '%.%' and job_number not like '%-%' and
(User_Def_Sequence = 152 or
User_Def_Sequence = 158 or
User_Def_Sequence = 159)
May 30, 2006 at 3:02 pm
You've got a couple of problems here... your initial IF NOT EXISTS will successfully run the follow-up query only if there are NO records at all in AP_Job_Cost_Security22. EXISTS only checks to see if a record exists which matches the SELECT statement inside the parenthesis. You can probably leave that out...
If I wanted to insert data into a table, but only records which did not exist already in the table, I'd use an outer join like:
INSERT table2 (value1, value2, value3)
SELECT t1.value1, t1.value2, t1.value3
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.value1 = t2.value1
AND t1.value2 = t2.value2
WHERE t2.value1 IS NULL
The left join forces all values from table1 to be shown, whether or not they match values in table2 (based on the criteria of the join). The WHERE statement indicates that only situations where the values are unmatched should be inserted.
It's important to note that this will only be able to be accurate if value1 is not allowed to be null, otherwise you'll have to choose another value (I'd recommend a non-nullable key).
Hope this helps.
May 30, 2006 at 3:32 pm
Thanks Aaron, i think it should be working now.
I was playing with the exist clause because I saw an example in a stored procedure, but I guess it was ment more for a single record match instead of what I'm trying to do.
May 31, 2006 at 7:27 am
This is a question for Aaron,
Would the following work also:
INSERT INTO AP_Job_Cost_Security22 (Operator_ID, Operator_Name, Email_Address, Master_Job, Company_Code)
SELECT DISTINCT O.Operator_ID,O.Operator_Name, O.Email_Address, U.Job_number, U.Company_Code
FROM JC_JOB_USER_FIELDS_DET_MC U INNER JOIN pa_operator_master O ON U.alpha_Field = O.Operator_Name
WHERE LTRIM(RTRIM(O.Email_Address)) <> ''
AND job_number not like '%.%'
AND job_number not like '%-%'
AND (User_Def_Sequence = 152 OR User_Def_Sequence = 158 OR User_Def_Sequence = 159)
AND NOT EXISTS (SELECT NULL FROM AP_Job_Cost_Security22
WHERE Operator_ID = O.Operator_ID
AND Operator_Name = O.Operator_Name
AND Email_Address = O.Email_Address
AND Master_Job = U.Job_number
AND Company_Code = U.Company_Code)
I am not sure what the primary keys on AP_Job_Cost_Security22 are, so I included each field in the 'NOT EXISTS' clause. If a primary key exists on the table, then SELECT statment in the 'NOT EXISTS' clause could be limited to just those fields.
Dave Novak
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply