Insert into second table if it doesnt exist in first table

  • Hi

    I have 2 tables with both have the same 'primary key' but completely different columns...

    I want to INSERT new records into ta second table when it doesn't exist in the first.

    I have tried the below code but get 'nulls' for the @newprojectid...

    Any help appreciated..

    DECLARE @newProjectId bigint

    INSERT INTO ProjectControl (ProjectId,InlcudeInForecast,ProjectCompleted,BudgetValue,ActualValue,BudgetHours,ActualHours,BillingValue,IsProspect)

    VALUES

    (@newProjectId,1,0,0,0,0,0,0,0)

    SELECT @newProjectId,project.id

    FROM project

    WHERE not exists(SELECT * FROM ProjectControl WHERE ProjectControl.ProjectId = project.id)

  • Try LEFT JOIN with NULLs

    SELECT @newProjectId,project.id

    FROM project

    LEFT JOIN ProjectControl

    ON ProjectControl.ProjectId = project.id

    WHERE project.id IS NULL

    {Edit : Gave a crap-load information before, please disregard any previous information if u have already read this post}

  • It's Gail's.

    Here it is : Left outer join vs NOT EXISTS[/url]

  • Check these out ( comment out NULLs in the table and see for yourself how things behave )

    declare @FirstTable Table( A int )

    insert into @FirstTable

    select 1

    union all select 2

    union all select 3

    union all select NULL

    union all select NULL

    declare @SecondTable Table( A int )

    insert into @SecondTable

    select 1

    union all select 2

    union all select NULL

    union all select NULL

    union all select 4

    union all select 5

    --== NOT EXISTS

    select *

    from @FirstTable FT

    where not exists

    ( select top 1 *

    from @SecondTable ST

    where FT.A = ST.A )

    --== LEFT JOIN with IS NULL

    select *

    from @FirstTable FT

    left join @SecondTable ST

    on FT.A = ST.A

    where ST.A is null

    --== WHERE NOT IN

    select *

    from @FirstTable FT

    where FT.A not in

    ( select ST.A

    from @SecondTable ST)

    --== EXCEPT

    select A

    from @FirstTable

    EXCEPT

    select A

    from @SecondTable

    {Edit : Added EXCEPT}

  • Many thanks bit I still get and error...

    Below revised statement as per your suggestion...

    DECLARE @newProjectId bigint

    INSERT INTO ProjectControl (ProjectId,InlcudeInForecast,ProjectCompleted,BudgetValue,ActualValue,BudgetHours,ActualHours,BillingValue,IsProspect)

    VALUES

    (@newProjectId,1,0,0,0,0,0,0,0)

    SELECT @newProjectId,project.Id

    FROM project

    LEFT JOIN ProjectControl ON ProjectControl.ProjectId = project.id

    WHERE project.id IS NULL

    returns error

    Msg 515, Level 16, State 2, Line 3

    Cannot insert the value NULL into column 'ProjectId', table 'HARVEST.dbo.ProjectControl'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    (0 row(s) affected)

  • In your logic, you are declaring @newProjectId, then immediately using it in an INSERT statement. It has NOT been assigned a value at that point. What value to you think it should have, or want it to have?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply