Insert or Update Record in SQL Server Depending on Whether it Already Exists.

This is a simple example on how you can use T-SQL to Update a record if it already exists, or add a new record if it doesn’t.

This example uses a table STOCK_ITEMS, with two fields ITEM_ID and ITEM_VALUE. If @ITEM_ID already exists in the table we update ITEM_VALUE with a new value (@ITEM_VALUE), if not we add a new record with @ITEM_ID and @ITEM_VALUE.

DECLARE @ITEM_ID VARCHAR(50)
DECLARE @ITEM_VALUE FLOAT

SET @ITEM_ID = 'ITM001'
SET @ITEM_VALUE = 100

IF @ITEM_ID IN (SELECT ITEM_ID FROM dbo.STOCK_ITEMS)
	BEGIN
		-- UPDATE
		UPDATE dbo.STOCK_ITEMS
			SET ITEM_VALUE = @ITEM_VALUE 
		WHERE ITEM_ID = @ITEM_ID  
	END
ELSE
	BEGIN
		-- ADD NEW
		INSERT INTO dbo.STOCK_ITEMS SELECT @ITEM_ID, @ITEM_VALUE
	END

In practice you would probably want to change this to a Stored Procedure that accepted the Variables as Parameters.

CREATE PROC up_add_update_stock_item
(
	@ITEM_ID VARCHAR(50),
	@ITEM_VALUE FLOAT
)AS 

IF @ITEM_ID IN (SELECT ITEM_ID FROM dbo.STOCK_ITEMS)
	BEGIN
		-- UPDATE
		UPDATE dbo.STOCK_ITEMS
			SET ITEM_VALUE = @ITEM_VALUE 
		WHERE ITEM_ID = @ITEM_ID  
	END
ELSE
	BEGIN
		-- ADD NEW
		INSERT INTO dbo.STOCK_ITEMS SELECT @ITEM_ID, @ITEM_VALUE
	END
Advertisements
This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s