August 27, 2011 at 9:06 pm
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)
August 27, 2011 at 10:20 pm
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}
August 27, 2011 at 10:24 pm
August 27, 2011 at 10:41 pm
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}
August 27, 2011 at 11:05 pm
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)
August 29, 2011 at 10:01 am
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