Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO

> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>