How to perform insert operation in SQL Server only when row does not exists?

Question

Table:# result
Name      Issues    Years      Value
Rani    Critical    2018        0.90
Rani    Critical    2018        0.00

Expected Output:-
Name      Issues    Year       Value
Rani    Critical    2018       0.90
Rani    Critical    2018       0.00
Rani        Null   2019        0.00


select Name=’Rani’,Issues=' Critical', years, ,Value from #result;

Insert #result (Name,years,Value) values(‘Rani’,year(getdate()),0)

I want to perform insert Operation on the #result table only when Current year(eg:-2019) Is not exists. If current year exists on #result table insert operation should not perform .

Possible duplicate of [SQL Server insert if not exists best practice](https://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice). The question is for SQL Server but the answer is standard sql and applies here too. — Joakim Danielson, Apr 16 '19 at 08:29

Venkataraman R · Accepted Answer · 2019-04-16T08:34:01.767

2

You can use IF NOT EXISTS to carry out this operation.

DECLARE @year INT = YEAR(GETDATE())
IF NOT EXISTS(SELECT 1 FROM #result WHERE Year = @year )
BEGIN
Insert #result (Name,years,Value) values(‘Rani’,@year,0)  
END

edited Apr 16 '19 at 08:34

answered Apr 16 '19 at 08:33

Venkataraman R

12,181
2
31
58

this isn't thread safe under default isolation level (and at any isolation level if not in a transaction) – Martin Smith Apr 16 '19 at 08:33
@MartinSmith, if we are using temporary table, is there any issue. Can you please tell me, what needs to be fixed. – Venkataraman R Apr 16 '19 at 08:36
Good point, As it is a locally scoped temp table this is fine. No other session can be inserting rows to it – Martin Smith Apr 16 '19 at 08:36
I don't think `GETDATE()` has any bearing in this question, although it's hard to tell from the question. – Nick.Mc Apr 16 '19 at 08:37
What's wrong with the solution in the question linked as duplicate of? – Joakim Danielson Apr 16 '19 at 08:38
@MartinSmith, thanks. Yes. we have used temporary table in transactions without issues, as it is scoped to the session. – Venkataraman R Apr 16 '19 at 08:39

How to perform insert operation in SQL Server only when row does not exists?

1 Answers1