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?
insert into ....
Or I've seen an example of is a SQL statement that said:
IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
SELECT 'This record already exists!'
INSERT INTO MyTable..........
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?