?
Solved

If row exists in table update else insert a new row

Posted on 2014-12-17
5
Medium Priority
?
46 Views
Last Modified: 2016-06-16
I have the following stored procedure:

USE [DATABASE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertNewRow]
      @StartDate DateTime = null

AS
  if @StartDate is null
        set @StartDate = getdate()

Begin

Insert [dbo].[TempPortfolioHistory]

EXEC CITYCRM_MSCRM.dbo.PortfolioHistory @EndDate = @StartDate

End

It adds a new row to the table.

Now the SP is going to be run more than once a day so I need to update the row if there is a row with the same date (first column in the row of the table), otherwise I need to do what the SP does currently.

Any help would be appreciated.
0
Comment
Question by:plainwrapname
[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
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1336 total points
ID: 40505467
Wasn't sure what type of UPDATE you'd need/want to do, but basically you'll want to use [NOT] EXISTS to test for the row.

ALTER PROCEDURE [dbo].[InsertNewRow]
       @StartDate DateTime = null

AS
SET NOCOUNT ON
IF @StartDate is null
    SET @StartDate = GETDATE()

IF NOT EXISTS(SELECT 1 FROM [dbo].[TempPortfolioHistory] WHERE StartDate = @StartDate)
Begin
    Insert [dbo].[TempPortfolioHistory]
    EXEC CITYCRM_MSCRM.dbo.PortfolioHistory @EndDate = @StartDate
End

GO --end of proc
0
 

Author Comment

by:plainwrapname
ID: 40505587
Sorry I was not clear.  If the row exists with the same date, I wish to overwrite that row instead of doing the insert.  I guess I need clarification on the use of the UPDATE command.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1336 total points
ID: 40505829
The only info directly available from the proc is the @StartDate, and that's used to do the search.  There's no data to "UPDATE".  You might need to change the stored proc to handle updates, or create a separate proc for updates and then you call the proc you need, either INSERT or UPDATE.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 664 total points
ID: 40506484
What returns the SP CITYCRM_MSCRM.dbo.PortfolioHistory ?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

800 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