Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-25 : 11:04:31
|
Tom writes "SQL seems like a simple an flexible language until you try to do something realistic. For example, suppose you want to insert a row of data in a table with a unique key constraint. You could just try to do the insert. But if a row with the same key already exists, then you get a constraint violation error (e.g. 2627, 2601, etc.). If you are using ODBC and/or stored procedure, things become worse. For instance, if the insert was in some conditional code inside of a stored procedure invoked from ODBC, then ODBC returns a SQL_ERROR code even if the stored procedure completes normally, and you will have a heck of a time trying to figure out which insert produced the constraint violation if there are other messages in the diagnostic info. You could produce your own error messages to deal with this, using raiserror, print, return, etc., but you might think a better way to go would be to suppress the error messages for the inserts you don't care about, and treat all others seriously. It seems reasonable, since transact-sql doesn't stop executing just because a statement fails. However, I've found no way to suppress error messages for a particular statment. (And if you can't suppress error messages, it makes it difficult to handle them generically.)
Anyway, this leads into the question of how to do inserts without incurring constraint violations.
Suppose you do the following:
if not exists(select col1 from table1 where col1 = @key)
insert table1 (col1) values (@key)
This doesn't always work because someone could come in and insert the row between the time you executed the select and the time you execute the insert.
Alternatively, you could do this:
begin tran
if not exists(select col1 from table1 (tablockx) where col1 = @key)
insert table1 (col1) values (@key)
commit
This works, but it locks the table from other writers (and readers) even in the case where the row is already there.
A third alternative is this:
if not exists(select col1 from table1 where col1 = @key)
begin
begin tran
if not exists(select col1 from table1 (tablockx) where col1 = @key)
insert table1 (col1) values (@key)
commit
end
This alternative only takes out the table lock if there is a chance the insert will be necessary. But then it must do the query again in case the row was inserted by someone else before the insert could be made.
Is there any way to do the insert safely in less steps?" |
|
|
|
|
|
|
|
insert idioms to avoid constraint error messages