Below is the table stuctures , i have a original table in to which i need to insert the data if it doesnt exists.
Create table @PRoducts(COmpanyID,PRoductName)
select 12345 'abc' union all
select 12345 'efg'
Create table @Program(ProgramID,NumberOFDays,CompanyID)
SELECT 55555,4,12345
Basically what i am trying to do is,i have a table by the name table3 in this table i need to update the number of days fields for the program id 55555, if that program does not exists then i need to insert the new row or else update the row if it exists.at
the same time i also need to check if that programid is associated with 2 productnames given in the above products table varible if not i need to insert the new row.
here is the table strucute for table3
create table table3(programid,productname,date,numberofdays)
select 55555,'abc',1900-01-01, 0 union all
select 66666,'efg',1900-01-01,0 union all
select 66666,'abc',1900-01-01,0
so,as it is seen in table3 that number of days is 0 for 55555 this need to be updated to 5 and also 55555 is not assoxciated with the product name 'efg' so i need to insert the new row for that with the default values.I am using below query but it is not
working.I am able to update the row if it exists but it is not inserting the new row if the given programname is not there in the table3
insert into table3
select i.programid
p.productname
1900-01-01 as date,
i.numberofdays
from @program i join table3 t on t.programid=i.programid
join @products pt on pt.companyid = i.companyid where i.productname <>pt.productname
Can someone please tell me how to resolve this issue. i know that i am missing something in the query.i did not include the update statement that i am using becuase i am fine with that.
