CODEWITHSUNDEEP
sqlt-sqlsql-insertnot-exists
dori2o
dori2o

Reputation: 31

MS SQL Server 2012 - Insert Values if row does not exist

I'm new to SQL and I need to check whether or not values in a row exist before I insert them.

I am attempting to insert details to several rows at once.

The code I have is as follows:

insert into [Test].[Projects]([TestID], [GroupID], [TestingID], [Grade])
values 
    (314, 9, 77, 2)
    ,(314, 9, 77, 3)
    ,(314, 9, 77, 4)
    ,(329, 2, 65, 2)
    ,(329, 2, 65, 3)
    ,(329, 2, 65, 4)
go

If someone could help me to insert these where the row values do not exist then I would be very grateful

Upvotes: 2

Views: 47

Answers (1)

GMB
GMB

Reputation: 222652

You could use the insert ... select syntax with a not exists condition that ensures that the very same record is not already there in the table:

insert into Test.Projects(TestID, GroupID, TestingID, Grade)
select v.*
from (values 
    (314, 9, 77, 2), 
    (314, 9, 77, 3), 
    (314, 9, 77, 4), 
    (329, 2, 65, 2), 
    (329, 2, 65, 3), 
    (329, 2, 65, 4)
) v(TestID, GroupID, TestingID, Grade)
where not exists (
    select 1 
    from Test.Projects p
    where 
        p.TestID = v.TestID 
        and p.GroupID = v.GroupID 
        and p.TestingID = v.TestingID
        and p.Grade = v.Grade
)

Upvotes: 2

Related Questions