October 20, 2014 at 1:18 pm
I have 2 tables and I need to check if emplid+ applicationId+Program id from first table does not exist in emplid+ApplicationID+programID another table then insert the record in the second table from first table
Else if does exist then compare another field and insert new record
Else if does exist then check another condition and insert new record.
When I try to use If statement for condition1
If(select emplid+ applicationId+Programid from table1 )not in (select emplid+ApplicationID+programID from Table 2)
Then insert into table 2
Select * from table1
I get the following error :
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I do this with If then else statement?
Thanks,
Blyzzard
October 20, 2014 at 2:15 pm
either the left or the right side of your if statement is returning more than one value. Execute them separately to see which one it is. Then, make them unique. You could say (select top (1) ...) = (select top(1) ...)
or you could do a join and look for results
if exists (
select ...
from a
join b
on a.<col> = b.<col>)
Then there are some matching rows.
Gerald Britton, Pluralsight courses
October 20, 2014 at 2:16 pm
What your statement is doing here is saying if <single scalar value> not in (<List of stuff)...
The problem is (select emplid+applicationid+ProgramID from table1) can (and does) return more than one value.
The best way to rewrite this query is a bit of a departure from thinking using the IN or NOT IN or EXIST commands, and to use a join instead. Try this:
insert into table2
select
emplid,
applicationid,
programid
from table1 a
left outer join table2 b
on a.emplid = b.emplid
and a.applicationid = b.applicationid
and a.programid = b.programid
where b.emplid is null
This is a common sql insertion paradigm which joins the source table (table1) to the destination table (table2) via a left outer join on the primary key (assuming the fields you've concatenated are the primary key). The where clause then selects values from table1 ONLY where there is NOT a record in table2 (where b.emplid is null)
October 20, 2014 at 2:33 pm
Thank you SSC-Enthusiastic. This works when the emplid does not exist in table 2 but how can I add the query when emplid is not null and I need to insert a new record if the value of a particular column in both the tables does not match?
October 20, 2014 at 2:41 pm
Can you give me an example of what you mean?
FWIW, although I only specifically checked whether emplid is null, it effectively is checking all the values that I joined on. Basically if any of the values (be it the emplid, the applicationid or the programid) do not match, you can infer this by checking any of the values. I chose emplid, but you could have checked applicationid, programid, or any combination thereof.
Assuming the check you're doing is against the primary key of table2, I think this is probably what you want. If I'm misinterpreting your problem, please let me know.
October 20, 2014 at 2:49 pm
There are 2 parts of this problem:
I am trying to insert the records in table 2 if the combination of (emplidApplicationID+programID ) from table1 does not exist in table2
or
If (emplidApplicationID+programID ) from table 1 does exist in table 2 but if table1.program<> table2.program then insert a new record with the new program from table1.
How can I insert the new record when the particular field does not match?
Thanks,
Blyzzard
October 20, 2014 at 2:54 pm
October 20, 2014 at 3:08 pm
I need to stress here that I'm making several leaps of logic about your database schema.
The primary keys of table1 and table2 plus the rules of how each are populated may change the light in which this problem is viewed. I stand by my assertion that you're probably looking for some variant on a left outer join checking for nulls in the target table. But when you say that the ProgramID can vary and that you're only doing a lookup on a few of the columns, now I'm starting to question whether my query holds water in your example.
For instance, if table1 can allow multiple emplid, applicationd, programid combinations, but table2 can only contains 1 emplid, application combination (not allowing a variant programid), you then have a problem of deduplication on table1 upon insertion into table two.
If both table1 and table2 are are keyed identically on emplid, applicationid and programid, but you only want one record per emplid/applicationid in table2, the PK on table2 should probably be re-evaluated.
If both table1 and table2 are keyed on emplid/applicationid and allow for variant programids as an attribute of each emplid/applicationid combo, the element of checking whether or not the values for programid between table1 and table2 are different is moot (you only need to know if the key values exist).
October 28, 2014 at 7:50 am
The query works fine when the emplid does not exist in Table2, so it inserts the new record.
Since I want to keep all the historic record in table2 and if the emplid changes the program, i need to enter the new program in table2 from table1, when I try to insert a record with the matching emplid,applicationid and programid and when the program is different from the program with current record in table2 then it works fine first time.
but when I run the query again, it inserts 2 rows for a record(1 each for old and new program).
This is an issue since I will have multiple duplicated in table2 for the records where program has changed.
Thanks,
Blyzzard
October 29, 2014 at 4:10 pm
amar_kaur16 (10/28/2014)
The query works fine when the emplid does not exist in Table2, so it inserts the new record.Since I want to keep all the historic record in table2 and if the emplid changes the program, i need to enter the new program in table2 from table1, when I try to insert a record with the matching emplid,applicationid and programid and when the program is different from the program with current record in table2 then it works fine first time.
but when I run the query again, it inserts 2 rows for a record(1 each for old and new program).
This is an issue since I will have multiple duplicated in table2 for the records where program has changed.
Unless I'm misreading this, I think you simply need to add all the possible columns to the left join.
insert into @table2
(emplID, applicationID, programID)
select
a.emplID,
a.applicationID,
a.programID
from @table1 a
left outer join @table2 b
on a.emplID = b.emplID
and a.applicationID = b.applicationID
and a.programID = b.programID
where b.emplID is null
This will add rows when any of the pertinent columns are new.
Don Simpson
October 30, 2014 at 5:38 pm
Take a look at the MERGE operator in Books Online. It will insert into the table when the record is not there and update the DESC or whatever other non-key fields when a record is found and DESC values differ. See if this helps you along or if you would like a example (in which case I would ask you to provide usable data and table definitions. )
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply