If not exist insert query to database

Question

I have the two following queries, the first one is directly inserting while the second query is checking if the record exists first, then if it does it will stop insert if not then it will insert.

Working

query1 = "INSERT INTO dbo.SAP_Mat_StoreBGA (Material,MaterialDescr) 
          VALUES ('" + row["Material"] + "','" + row["MaterialDescr"] + "')";

Not Working

query1 = "IF NOT EXISTS (
   INSERT INTO dbo.SAP_Mat_StoreBGA (Material,MaterialDescr)" + " 
   VALUES ('" + row["Material"] + "','" + row["MaterialDescr"] + "')
)";

Please help me get the "not working" query working.

What exactly do you expect to happen when running your second query? — pritaeas, Mar 17 '20 at 08:42
you should check `SELECT` statement in `NOT EXISTS` then only do `INSERT` statement instead of `INSERT` in `NOT EXISTS` — Se0ng11, Mar 17 '20 at 08:56
Read the [docs](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15) — Ilyes, Mar 17 '20 at 09:00
Does this answer your question? [SQL Server Insert if not exist](https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist) — Ilyes, Mar 17 '20 at 09:03
What does not working mean? What happens? What do you expect to happen? What sample data reproduces the issue? — Dale K, Mar 17 '20 at 09:06

Venkataraman R · Answer 1 · 2020-03-17T09:27:00.287

The way you are framing the query is wrong. It can lead to SQL Injection attacks. I would suggest you to use SqlParameters.

string commandText = "IF NOT EXISTS(SELECT * FROM dbo.SAP_Mat_StoreBGA WHERE "
          + "Material = @Material " +
          + " AND MaterialDescription = @MaterialDescription)"
          + "BEGIN"
          + "INSERT INTO dbo.SAP_Mat_StoreBGA (Material,MaterialDescr) "
          + "VALUES (@Material ,@MaterialDescription)"
          + "END";
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = @commandText;
cmd.Parameters.Add("@Material", SqlDbType.Varchar,30).Value=row["Material"];
cmd.Parameters.Add("@MaterialDescription",SqlDbType.Varchar,255).Value=
                                                   row["MaterialDescr"];

In the background, it will be using sp_executesql for executing parametrized statement.

exec sp_executesql N'IF NOT EXISTS(SELECT * FROM dbo.SAP_Mat_StoreBGA where 
Material = @Material 
BEGIN
AND MaterialDescription = @MaterialDescription)
INSERT INTO dbo.SAP_Mat_StoreBGA (Material,MaterialDescr) 
VALUES (@Material ,@MaterialDescription)
END', N'@Material VARCHAR(30),@MaterialDescription VARCHAR(255)',@Material='ABC' 
,@MaterialDescription='ABC Description'

Although the use of `AddParameterWithValue` is [discouraged](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Dale K, Mar 17 '20 at 09:14
@DaleK, agreed. modified my code to reflect changes. thanks for enlightening me. — Venkataraman R, Mar 17 '20 at 09:27

score 0 · Answer 2 · answered Mar 17 '20 at 17:19

Here, I made you and example, I hope it helps, if not at least maybe it would lead you to an answers.

drop table #testMr
create table #testMr(val1 varchar (20),val2 numeric)

declare @param1 varchar(20)
declare @param2 numeric

set @param1='dog'
set @param2=10

--here it will insert that values if not exists
if(select count(*) from #testMr where val1=@param1 and val2=@param2)=0
begin
    insert into #testMr values(@param1,@param2)
end
select * from #testMr
set @param1='dog'
set @param2=10
--this is an example when it wont insert the values
if(select count(*) from #testMr where val1=@param1 and val2=@param2)=0
begin
    insert into #testMr values(@param1,@param2)
end
select * from #testMr
set @param1='cat'
set @param2=10
--and this is and example when it will insert because its a diferent values
if(select count(*) from #testMr where val1=@param1 and val2=@param2)=0
begin
    insert into #testMr values(@param1,@param2)
end
select * from #testMr

score -1 · Answer 3 · edited Mar 17 '20 at 09:05

-1

Presuming your table have id as Primary key. You can use the below SQL query to check if the record with that id exists.

Select count(*) from tableA where id = 'yourId'

edited Mar 17 '20 at 09:05

Suraj Kumar

5,547
8
20
42

answered Mar 17 '20 at 08:45

hoangnh

249
4
13

If not exist insert query to database

3 Answers3