?
Solved

If Row Exists Update, Else Insert in SQL Server

Posted on 2011-02-21
14
Medium Priority
?
1,534 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:Auerelio Vasquez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 34947018
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
ID: 34947077
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 41

Expert Comment

by:Sharath
ID: 34947092
Can you post some sample data from your table and how exactly you want to update/insert the new records?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34947141
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:Ephraim Wangoya
ID: 34947186
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:Auerelio Vasquez
ID: 34947198
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 41

Accepted Solution

by:
Sharath earned 1000 total points
ID: 34947244
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
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 34947405
How do i execute that sp ?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34947469
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:Auerelio Vasquez
ID: 34947569
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:Auerelio Vasquez
ID: 34947572
it's actually not  from an app, but from a select statement

0
 
LVL 41

Assisted Solution

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

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 500 total points
ID: 34947961

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 500 total points
ID: 34958582
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

743 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