2

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

Improve this question

1 Answer 1

Reset to default
2

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 )
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

OK I got that working, but... If TestID was the Variable vTestID, would it be possible to enter these values to multiple vTestID values within a single run of the script? So for instance if I wanted to run a code where the values (314, 9, 77, 2), were where vTestID is 101, then values (314, 9, 77, 3), set where vTestID is 102,etc I've been looking at Value Tables but unsure if this would work? Ideally I'd want to set all variables at once. Thanks

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.