SQL multiple columns makes row unique

VikingOnline
VikingOnline used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,
You can create a constraint as follows:


CREATE TABLE [dbo].[Table1] ADD  CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
      
      [Column1] ASC,
      [Column1] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


and I suppose this shall take care of all your issues.
Carl TawnSenior Systems and Integration Developer

Commented:
If you were using a stored procedure rather than inline SQL code, then I would perform the check first before attempting the insert - it's cleaner to check for a possible issue if you can rather than relying on it failing.

If you're in a stored procedure then the overhead of checking first is negligible. If, on the other hand, you are running two separate statements from your VB app, then that will or course create additional network bandwith and delay the response to your app.

Author

Commented:
Hi Carl, so what you are saying is create a stored procedure and in the SQL query in the stored procedure, test if it exists before doing an insert?

So don't create two calls to the SQL server from the VB code, with one Select statement and one insert statement? But with a stored procedure, I can do both in one call to the SQL server?

That is done by a SQL query in the lines of:

IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
BEGIN
SELECT 'This record already exists!'
END
ELSE
BEGIN
INSERT INTO MyTable..........
SELECT 'Record inserted!'
END

What would be the VB.NET code be?
Create a normal data reader to read the return of the select statements? Pass the values for the insert as parameters, but other than that treat it as a sql data reader?
Senior Systems and Integration Developer
Commented:
If you're just doing an INSERT and getting a single return value then you can use ExecuteScalar rather than using a DataReader.

Depending on your requirements, you could return a status number (-1 if it didn't insert, or the ID of the new row if it did). For example:
IF EXISTS(SELECT 'True' FROM MyTable WHERE xxxxx = @xxxxx
BEGIN
    SELECT -1
END
ELSE
BEGIN
   INSERT INTO MyTable..........
   SELECT SCOPE_IDENTITY()
END

Open in new window

From VB it would basically be something along the lines of:
        Dim cn As New SqlConnection("connection string")
        Dim cmd As New SqlCommand("spInsertARecord", cn)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Param1", "some value")
        '// repeat for other parameters

        Try

            cn.Open()
            Dim result As Integer = DirectCast(cmd.ExecuteScalar(), Integer)
            If result > -1 Then
                '// record was inserted
            Else
                '// record already exists
            End If

        Catch e As SqlException
            '// do something with the exception
        Finally

            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If

        End Try

Open in new window

Obviously you'd need to change the stored procedure, and parameter names, to suit but this should demonstrate the basic structure of the code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial