Solved

If Row Exists Update, Else Insert in SQL Server

Posted on 2011-02-21
14
1,323 Views
Last Modified: 2012-05-11
I've seen this used, before in SQL Server. (code attached). I understand that it inserts if the record doesn't exisit, and updates if it does. I'm tyring to do something slightly different, where,

i'm checking the AccountID and another column (OtherReIn)

the data is built from views, and put into a temp table. So, going in to the existing table, if that accountID exists in the temp table, compare the otherrein column, if it's different overwrite it, and upodate the lastmodifieddate column with getdate() if it doesn't exist just insert the record.


CREATE PROCEDURE usp_INSERTUPDATEEMP
(
 @EmpID AS INT,
 @LastName AS NVARCHAR(20),
 @FirstName AS NVARCHAR(20)
)
AS
BEGIN
    IF EXISTS (SELECT EmployeeID FROM Employees WHERE EmployeeID = @EmpID)
    BEGIN
        -- Write your update query
        UPDATE  Employees
        SET  FirstName = @FirstName, LastName = @LastName
        WHERE   EmployeeID = @EmpID  
    END
    ELSE
    BEGIN
       -- Write your insert query
       INSERT INTO Employees (EmployeeID, FirstName, LastName)
       VALUES ( @EmpID, @FirstName, @LastName )
    END
END

Open in new window

0
Comment
Question by:basile
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 1

Author Comment

by:basile
Comment Utility
another thing, if the record already exists, and the otherReIn field has NOT changed, then do nothing and move to the next record.
0
 
LVL 11

Expert Comment

by:dougaug
Comment Utility
See if this works:

CREATE PROCEDURE usp_INSERTUPDATEEMP
(
 @accountID AS INT,
 @otherrein AS VARCHAR(20) -- Type here the otherrein datatype
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM temptable WHERE AccountId = @accountID)
    BEGIN
        -- Write your update query
        UPDATE  temptable
        SET  OtherReIn = @otherrein, LastModifiedDate = getdate()
        WHERE AccountId = @accountID
          and OtherReIn <> @otherrein
    END
    ELSE
    BEGIN
       -- Write your insert query
       INSERT INTO temptable (AccountId, OtherReIn, LastModifiedDate)
       VALUES (  @accountID,  @otherrein, null)
    END
END
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Can you post some sample data from your table and how exactly you want to update/insert the new records?
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
try something like this
CREATE PROCEDURE usp_INSERTUPDATEEMP
(
 @AccountID AS INT,
 @Otherrin AS VARCHAR(20),
 @lastmodifieddate AS DATETIME
)
AS
BEGIN
	declare @acctid int
	declare @othr varchar(20)
	declare @moddate datetime
  
    SELECT @acctid = AccountID, @othr = Otherrin, @moddate = lastmodifieddate 
    FROM TABLE1 
    WHERE AccountID = @AccountID
    
	IF @acctid is null 
	BEGIN
	  INSERT INTO TABLE1 (ccountID, Otherrin, lastmodifieddate)
	  VALUES ( @AccountID, @Otherrin, GETDATE() )
	END
	ELSE IF @Otherrin = @othr
	BEGIN
		UPDATE  TABLE1
		SET  AccountID = @AccountID, lastmodifieddate = @lastmodifieddate
		WHERE   AccountID = @AccountID  
    END
END

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
Mistake with account id
, try
CREATE PROCEDURE usp_INSERTUPDATEEMP
(
 @AccountID AS INT,
 @Otherrin AS VARCHAR(20),
 @lastmodifieddate AS DATETIME
)
AS
BEGIN
	declare @acctid int
	declare @othr varchar(20)
	declare @moddate datetime
  
    SELECT @acctid = AccountID, @othr = Otherrin, @moddate = lastmodifieddate 
    FROM TABLE1 
    WHERE AccountID = @AccountID
    
	IF @acctid is null 
	BEGIN
	  INSERT INTO TABLE1 (ccountID, Otherrin, lastmodifieddate)
	  VALUES ( @AccountID, @Otherrin, GETDATE() )
	END
	ELSE IF @Otherrin = @othr
	BEGIN
		UPDATE  TABLE1
		SET  Otherrin= @Otherrin, lastmodifieddate = @lastmodifieddate
		WHERE   AccountID = @AccountID  
    END
END

Open in new window

0
 
LVL 1

Author Comment

by:basile
Comment Utility
Sharath:


The Data Looks Like this:

AccountID                        OtherReIn                       CreatedDate              LastModifiedDate
7yrhf895ijkgo096ir           1;SAM-R,12/20/2011      02/20/2011                02/21/2011


These are inserted from a view daily. If the record exists, and nothing has changed, i want to do nothing, just leave the lastmodifieddate blank.

However, if the record exists, i want to compare to accountID and the OtherReIn data is different, overwrite that record, and modify the lastmodifieddate to getdate()

If the record doesnt' exist, then just insert it, and update the createdDate
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility
try like this.
CREATE PROCEDURE USP_INSERTUPDATEEMP( 
                @accountID AS INT, 
                @otherrein AS VARCHAR(20) -- Type here the otherrein datatype  
) 
AS 
  BEGIN 
    IF EXISTS (SELECT 1 
                 FROM temptable 
                WHERE AccountId = @accountID) 
      BEGIN 
        IF NOT EXISTS (SELECT 1 
                         FROM temptable 
                        WHERE AccountID = @accountID 
                              AND OtherReIn = @otherrein) 
          BEGIN 
            -- Write your update query 
            UPDATE temptable 
               SET OtherReIn = @otherrein, 
                   LastModifiedDate = GETDATE() 
             WHERE AccountId = @accountID 
          END 
      END 
    ELSE 
      BEGIN 
        -- Write your insert query 
        INSERT INTO temptable 
                   (AccountId, 
                    OtherReIn, 
                    CreatedDate, 
                    LastModifiedDate) 
             VALUES(@accountID,@otherrein,GETDATE(),NULL) 
      END 
  END

Open in new window

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Author Comment

by:basile
Comment Utility
How do i execute that sp ?
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
execute USP_INSERTUPDATEEMP
        @AccountID = 123,
        @OtherRein = '1;SAM-R,12/20/2011'

Open in new window

If your AccountID is not int, alter the proc to define the @AccountID as VARCHAR.
0
 
LVL 1

Author Comment

by:basile
Comment Utility
ahh, yes, but these are coming from a table, multiple rows.


so, the view is called MM_PROD2

i want to insert everything from MM_PROD2 into MM_Other_Info, but run those checks......
0
 
LVL 1

Author Comment

by:basile
Comment Utility
it's actually not  from an app, but from a select statement

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
Comment Utility
Do you have read access on MM_PROD2? If so , you can use that view inside SP.
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 125 total points
Comment Utility

You can use a cursor to do that
CREATE PROCEDURE USP_INSERTUPDATEEMP
(
 @AccountID AS INT,
 @OtherReIn AS VARCHAR(20)
)
AS
BEGIN
	DECLARE @LocalID INT
	DECLARE @LocalOthrRein varchar(20)
	  
    SELECT @LocalID = AccountID, @LocalOthrRein = OtherReIn
    FROM MM_Other_Info 
    WHERE AccountID = @AccountID
    
	IF @LocalID is null 
	BEGIN
	  INSERT INTO MM_Other_Info (AccountID, OtherReIn, CreatedDate)
	  VALUES ( @AccountID, @OtherReIn, GETDATE() )
	END
	ELSE IF @OtherReIn <> @LocalOthrRein
	BEGIN
		UPDATE  MM_Other_Info
		SET  OtherReIn = @OtherReIn, lastmodifieddate = GETDATE()
		WHERE  AccountID = @AccountID  
    END
END

Open in new window

DECLARE
    @AccountID int,
    @OtherReIn varchar(20)

DECLARE mycursor CURSOR READ_ONLY
FOR SELECT AccountID, OtherReIn
FROM MM_PROD2

OPEN mycursor

FETCH NEXT FROM mycursor
INTO @AccountID, @OtherReIn

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE USP_INSERTUPDATEEMP @AccountID, @OtherReIn
    FETCH NEXT FROM mycursor
    INTO @AccountID, @OtherReIn
END

CLOSE mycursor
DEALLOCATE mycursor

Open in new window

0
 
LVL 6

Assisted Solution

by:Rajesh_mj
Rajesh_mj earned 125 total points
Comment Utility
Can you please check the code below meets ur conditions?

Note : There might be syntax errors as I do not have SQL is not in my machine now.
CREATE PROCEDURE USP_INSERTUPDACCTINFO AS

BEGIN

INSERT INTO MM_Other_Info (AccountID,OtherReIn,CreatedDate,lastmodifieddate)
SELECT AccountID,OtherReIn,Getdate(),GetDate() FROM MM_PROD2 MP WHERE NOT EXISTS 
(SELECT * FROM MM_Other_Info MO WHERE MO.AccountID = MP.AccountID)

UPDATE MM_Other_Info SET lastmodifieddate = GETDATE() 
WHERE AccountID <> OtherReIn

END

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now