We help IT Professionals succeed at work.
Get Started

SQL multiple columns makes row unique

382 Views
Last Modified: 2014-05-02
Hey guys,
I have a hopefully simple question, although it became very long to explain it.
I'm looking for the best practice and most efficient solution to do this.

I want to create a SQL table, and in this table two columns makes the row unique when looked at together. And this same entry cannot happen more than once. So each column can have repeats, but together the value of column A + B can only happen once.

I can add a UNIQUE constraint in the SQL table for both columns together. I assume that will make sure that you can have the same value in column A multiple times, and the same value in column B multiple times, but only one A+B.

When I do the VB.NET code to do the insert of new rows into the table, and I know it's a risk that someone has already updated part of this data, should I do a test first to see if the record exists and only pass the insert statement if it does not exist? Or is it better to send the insert statement, and handle the insert error from the SQL server?

I also want to publish a message back to the user like "483 rows out of 500 was updated. 17 rows already existed."

My first thought was:

VB code for:
SELECT statement to see if exist
If not exist, INSERT INTO statement
If exists, add 1 to variable counting number of rows not updated

My second thought was that this must create extra traffic to the SQL server, and I think it's better to let the SQL server test if it can be updated based on the unique constraint, and then push back an error if it cannot

VB code for:
INSERT INTO statement
If error code row already exist, add 1 to variable counting number of rows not updated
Else if other error code, handle error

And what is the best way to handle that on the SQL server side?
I can try to update, and catch if it fails?
Begin try
insert into ....
End try
Begin catch
.........


Or I've seen an example of is a SQL statement that said:
IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
INSERT INTO MyTable..........
END

I've never used the IF EXISTS SQL statement, but assuming it's a valid option I have 3 possible ways to do this and all 3 would work. This is a process where I might add several hundred or thousand rows into the SQL table, so I'm looking at what would be the most efficient and best way of doing this?
Comment
Watch Question
Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE