If Row Exists Update, Else Insert in SQL Server

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

LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
 
SharathData EngineerCommented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
another thing, if the record already exists, and the otherReIn field has NOT changed, then do nothing and move to the next record.
0
 
dougaugCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
SharathData EngineerCommented:
Can you post some sample data from your table and how exactly you want to update/insert the new records?
0
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
How do i execute that sp ?
0
 
SharathData EngineerCommented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
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
 
Auerelio VasquezETL DeveloperAuthor Commented:
it's actually not  from an app, but from a select statement

0
 
SharathData EngineerCommented:
Do you have read access on MM_PROD2? If so , you can use that view inside SP.
0
 
Ephraim WangoyaCommented:

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
 
Rajesh_mjCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.