Calling insert once a day

Question

I'm looking to insert data into the database only once per day. The problem is that I want the sql query to check this (as the code is untouchable)

Currently any time anyone goes to a certain web page it calls the spInsertRate procedure.

INSERT INTO dbo.LU_ExchangeRates 
(exch_Date,exch_Currency,exch_Rate) 
VALUES(@exchDATE,@exchCurrency,@exchRate)

I'm guessing I have to do a select statement first, and if the select statements does not return a row, that means to run the insert statement. I just can't figure out how to code for this.

You may have some design issues if this happens `Currently any time anyone goes to a certain web page it calls the spInsertRate procedure.` What rate is being inserted, or once for each currency?? — RichardTheKiwi, Jan 24 '11 at 21:24

score 4 · Answer 1 · answered Jan 24 '11 at 21:08

4

I'd put a unique constraint on the exch_Date column, then have spInsertRate run the insert and catch the exception if it fails.

answered Jan 24 '11 at 21:08

Harper Shelby

16,475
2
44
51

Specifically stated that front end code is not to be changed. – RichardTheKiwi Jan 24 '11 at 22:00
2

This has nothing to do with the front end code. There are T-SQL exceptions that are raised when you violate a constraint. These can be caught inside the SP, and swallowed as appropriate. – Harper Shelby Jan 25 '11 at 15:33

score 4 · Accepted Answer · answered Jan 24 '11 at 21:08

if you must do it as single SQL this will work

INSERT INTO dbo.LU_ExchangeRates 
(exch_Date,exch_Currency,exch_Rate) 
SELECT
   @exchDATE,@exchCurrency,@exchRate
WHERE
   NOT EXISTS ( 
        SELECT * from  dbo.LU_ExchangeRates   
        WHERE   
            exch_Date =  @exchDATE and exch_Currency = @exchCurrency)

but its more common to see

IF  NOT EXISTS ( 
            SELECT * from  dbo.LU_ExchangeRates   
            WHERE   
                exch_Date =  @exchDATE and exch_Currency = @exchCurrency)

    INSERT INTO dbo.LU_ExchangeRates 
    (exch_Date,exch_Currency,exch_Rate) 
    VALUES
       @exchDATE,@exchCurrency,@exchRate

score 1 · Answer 3 · answered Jan 24 '11 at 21:09

1

IF NOT EXISTS (SELECT * FROM dbo.LU_ExchangeRates WHERE exch_Date = @exchDATE)
BEGIN
INSERT INTO dbo.LU_ExchangeRates (exch_Date,exch_Currency,exch_Rate)  VALUES(@exchDATE,@exchCurrency,@exchRate) 
END

answered Jan 24 '11 at 21:09

Matt

2,078
2
27
40

score 1 · Answer 4 · answered Jan 24 '11 at 21:19

1

You can define a composite unique index with the IGNORE_DUP_ROWS option (or what's its name), and then any failed inserts will be silently discarded.

This can be done on Sybase ASE, maybe not on MS SQL server.

answered Jan 24 '11 at 21:19

knb

9,138
4
58
85

score 0 · Answer 5 · answered Jan 24 '11 at 21:10

0

I would do a single query that does its insert if it doesn't find today's date in the table. That way you don't have a race between the select an the insert. Maybe

INSERT INTO dbo.LU_ExchangeRates (...)
SELECT @exchDate, ...
 WHERE NOT EXISTS (SELECT *
                     FROM dbo.LU_ExchangeRates
                    WHERE exch_date >= @exchDate
                     AND exch_Currency != @exchCurrency)

answered Jan 24 '11 at 21:10

n8wrl

19,439
4
63
103

[This still does have a race condition.](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) – Martin Smith Jan 24 '11 at 21:29

Calling insert once a day

5 Answers5