Sql Server Return bool if item is available in table

Question

i have a table named tblStockManagement in my database. i have two columns named Client_ID and FoldingID.

i want to insert values to the columns, but first check the value if it is already exist in table or not. because then i will update the quantity of item instead of making new row in table.

i have method in my mind that select all data by given foldingID and Client_ID and see if dataset has 0 rows then data is not present already in table. but suggest me any easiest and reliable way to check that..

Answer 1

There is a common pattern for this called an UPSERT. In Sql Server, you perform an UPSERT using the MERGE statement.

MERGE tblStockManagement AS target
    USING (SELECT @clientID, @foldingId, @quantity) AS source (clientID, foldingID, quantity)
    ON (target.clientID = source.clientID AND target.foldingID = source.foldingID)
WHEN MATCHED THEN 
    UPDATE SET quantity = source.quantity
WHEN NOT MATCHED THEN
    INSERT (clientID, foldingID, quantity)
    VALUES (source.clientID, source.foldingID, source.quantity);

Answer 2

As Joel said in his answer, this is a normal problem in SQL which is called a UPSERT. However, if you are using a version of Sql Server that does not access to MERGE (2005 or older) you can accomplish the same goal "the long way" by doing the following.

BEGIN TRANSACTION
IF EXISTS(SELECT 1 FROM tblStockManagement WITH (UPDLOCK, HOLDLOCK) WHERE clientID = @clientID and foldingID = @foldingID)
BEGIN
    UPDATE tblStockManagement 
        SET quantity = @quantity 
        WHERE clientID = @clientID and foldingID = @foldingID
END
ELSE
BEGIN
    INSERT INTO tblStockManagement (clientID, foldingID, quantity)
        VALUES (@clientID, @foldingID, @quantity);
END
COMMIT

Doing the WITH (UPDLOCK, HOLDLOCK) on the select is very important, if you forget it you could either end up with queries that do not protect you from inserting in duplicate rows or potentially cause deadlocks (Depending on your database isolation level you have set for the connection).

Answer 3

Check manually,

First, check if Client_ID and FoldingID exists on database, if entry != null, don't add new, else, add new...