Solved

If row exists in table update else insert a new row

Posted on 2014-12-17
5
43 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 334 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 334 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 50

Assisted Solution

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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 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