[Resolved] Insert if not exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Insert if not exist

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-12 : 15:36:22

Have following code that I need to modify and only insert into #TempTable1 if BatchGuid does not exist. Thank you.


begin
insert into #TempTable1
                        (
                          BatchGuid,
                          BatchName, 
                          ReportDate,
                          JobNumber,
                          ReportStatus,
                          ReportOwner,
                          ApprovalLevel
                        )
select                              distinct(Event.BatchGuid) as BatchGuid, 
                                    Batch.Name as BatchName,
                                    Batch.ReportDate as ReportDate,
                                    Job.CompanyJobId as JobNumber,
                                    EventStatusType.Name as ReportStatus,
                                    Account.UserName as ReportOwner,
                                    EventApprovalLevel.Name as ApprovalLevel
from    [VGIWPSQL2].goLabor30.dbo.event 
        inner join [VGIWPSQL2].goLabor30.dbo.Batch on Batch.BatchGuid = Event.BatchGuid
        inner join [VGIWPSQL2].goLabor30.dbo.Job on Job.JobGuid = Event.JobGuid
        inner join [VGIWPSQL2].goLabor30.dbo.EventStatusType on EventStatusType.EventStatusTypeGuid = Event.EventStatus
	    inner join [VGIWPSQL2].goLabor30.dbo.Account on Account.AccountGuid = Batch.AccountGuid
        inner join [VGIWPSQL2].goLabor30.dbo.EventApprovalLevel on EventApprovalLevel.EventApprovalLevelGuid = Event.EventApprovalLevelGuid
where   (@Owner IS Null or Account.Username = @Owner or Account.Username like replace(@Owner,'*','%')) and 
        (@DateFrom IS  Null OR Batch.Reportdate >=  @DateFrom)and
        (@DateTo IS Null OR Batch.ReportDate <= @DateTo) and
        (@Job IS Null or Job.CompanyJobId = @Job or Job.CompanyJobId like replace(@Job,'*','%')) and
        (EventStatusType.Name = @StatusSelect3) and
        (EventApprovalLevel.Name = @ApprovalLevel1 or EventApprovalLevel.Name = @ApprovalLevel2) 
order by Batch.Name
SET @RetValue = '0'
end

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 03:41:35

Add the red part in your query

quote:

Originally posted by snufse

Have following code that I need to modify and only insert into #TempTable1 if BatchGuid does not exist. Thank you.


begin
insert into #TempTable1
                        (
                          BatchGuid,
                          BatchName, 
                          ReportDate,
                          JobNumber,
                          ReportStatus,
                          ReportOwner,
                          ApprovalLevel
                        )
select                              distinct(Event.BatchGuid) as BatchGuid, 
                                    Batch.Name as BatchName,
                                    Batch.ReportDate as ReportDate,
                                    Job.CompanyJobId as JobNumber,
                                    EventStatusType.Name as ReportStatus,
                                    Account.UserName as ReportOwner,
                                    EventApprovalLevel.Name as ApprovalLevel
from    [VGIWPSQL2].goLabor30.dbo.event 
        inner join [VGIWPSQL2].goLabor30.dbo.Batch on Batch.BatchGuid = Event.BatchGuid
        inner join [VGIWPSQL2].goLabor30.dbo.Job on Job.JobGuid = Event.JobGuid
        inner join [VGIWPSQL2].goLabor30.dbo.EventStatusType on EventStatusType.EventStatusTypeGuid = Event.EventStatus
	    inner join [VGIWPSQL2].goLabor30.dbo.Account on Account.AccountGuid = Batch.AccountGuid
        inner join [VGIWPSQL2].goLabor30.dbo.EventApprovalLevel on EventApprovalLevel.EventApprovalLevelGuid = Event.EventApprovalLevelGuid
where   (@Owner IS Null or Account.Username = @Owner or Account.Username like replace(@Owner,'*','%')) and 
        (@DateFrom IS  Null OR Batch.Reportdate >=  @DateFrom)and
        (@DateTo IS Null OR Batch.ReportDate <= @DateTo) and
        (@Job IS Null or Job.CompanyJobId = @Job or Job.CompanyJobId like replace(@Job,'*','%')) and
        (EventStatusType.Name = @StatusSelect3) and
        (EventApprovalLevel.Name = @ApprovalLevel1 or EventApprovalLevel.Name = @ApprovalLevel2) 
AND Event.BatchGuid NOT IN ( SELECT BatchGuid FROM #TempTable1 )
order by Batch.Name
SET @RetValue = '0'
end