none
inserting rows into a table if it does not exists RRS feed

  • Question

  • 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.
    Thursday, July 14, 2011 7:14 PM

Answers

  • basically:

    insert into tableB (...)

    select a.* from tableA a left join TableB b on
    a.ID = b.ID
    where b.ID is null

    Or use MERGE (have a look in BOL)

    • Proposed as answer by Naomi NModerator Thursday, July 14, 2011 8:55 PM
    • Marked as answer by anki2010 Thursday, July 14, 2011 11:54 PM
    Thursday, July 14, 2011 8:06 PM