CODEWITHSUNDEEP
sqlsql-server-2005
Kyle Johnson
Kyle Johnson

Reputation: 765

Update or insert data depending on whether row exists

I have a collection of rows that I get from a web service. Some of these rows are to be inserted, some are updates to existing rows. There is no way of telling unless I do a query for the ID in the table. If I find it, then update. If I don't, then insert.

Select @ID from tbl1 where ID = @ID

IF @@ROWCOUNT = 0
BEGIN
Insert into tbl1
values(1, 'AAAA', 'BBBB', 'CCCC', 'DDD')
END
ELSE
BEGIN
UPDATE tbl1
SET 
A = @AAA,
B = @BBB,
C = @CCC,
D = @DDD
WHERE ID = @ID
END

I am trying to figure out the most effient way to update/insert these rows into the table without passing them into a stored procedure one at a time.

UPDATE 1

I should have mentioned I am using SQL Server 2005. Also if I have 300 records I don't want to make 300 stored procedure calls.

Upvotes: 1

Views: 1737

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Instead of paying for a seek first and then updating using another seek, just go ahead and try to update. If the update doesn't find any rows, you've still only paid for one seek, and didn't have to raise an exception, but you know that you can insert.

UPDATE dbo.tbl1 SET 
A = @AAA,
B = @BBB,
C = @CCC,
D = @DDD
WHERE ID = @ID;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.tbl1(ID,A,B,C,D)
    VALUES(@ID,@AAA,@BBB,@CCC,@DDD);
END

You can also look at MERGE but I shy away from this because (a) the syntax is daunting and (b) there have been many bugs and several of them are still unresolved.

And of course instead of doing this one @ID at a time, you should use a table-valued parameter.

CREATE TYPE dbo.tbl1_type AS TABLE
( 
  ID INT UNIQUE,  
  A <datatype>,
  B <datatype>,
  C <datatype>,
  D <datatype>
);

Now your stored procedure can look like this:

CREATE PROCEDURE dbo.tbl1_Update
  @List AS dbo.tbl1_type READONLY
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE t 
    SET A = i.A, B = i.B, C = i.C, D = i.D
  FROM dbo.tbl1 AS t
  INNER JOIN @List AS i
  ON t.ID = i.ID;

  INSERT dbo.tbl1
  SELECT ID, A, B, C, D
    FROM @List AS i
    WHERE NOT EXISTS 
    (
      SELECT 1
      FROM dbo.tbl1 WHERE ID = i.ID
    );
END
GO

Now you can just pass your DataTable or other collection from C# directly into the procedure as a single parameter.

Upvotes: 1

Raghavan
Raghavan

Reputation: 637

what i understand is this :

at the front end u issue a single sql statement

ArrayofIDsforInsert = select ID from tbl1 where ID not in ( array of ids at the front end)
ArrayofIDsforUpdate = (IntialArrayofids at frontend) - (ArrayofIdsforInsert)

one insert into table and one update table...

now call the insert into table with ArrayofIds for insert
call the update table with ArrayofIds for update..

Upvotes: 0

Ranjit Singh
Ranjit Singh

Reputation: 3735

the most efficient way will be first try to update the table if it returns 0 row updated then only do insertion. for ex.

UPDATE tbl1
    SET 
    A = @AAA,
    B = @BBB,
    C = @CCC,
    D = @DDD
    WHERE ID = @ID    
IF @@ROWCOUNT = 0
    BEGIN
    Insert into tbl1
    values(1, 'AAAA', 'BBBB', 'CCCC', 'DDD')
    END
    ELSE
    BEGIN

END

Upvotes: 2

GeorgeVremescu
GeorgeVremescu

Reputation: 1253

From the collection of rows you get from the server find out which ones are already there:

select @id from tbl1 where id in (....)

Then you have a list of ids that are in the table and one that there are not in the table. You will have then 2 batch operations: one for update, the other for insert.

Upvotes: 0

Related Questions