Solved

If row exists in table update else insert a new row

Posted on 2014-12-17
5
32 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
  • 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 46

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

863 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

19 Experts available now in Live!

Get 1:1 Help Now