CODEWITHSUNDEEP
c#sqlsql-server
Franklin Joseph Moormann
Franklin Joseph Moormann

Reputation: 71

creating a custom constraint in sql

I am using sql on a C# application and my table consists of stock quotes. The columns that I'm concerned with are called Symbol and Date. What I'm trying to do is restrict a row from adding to the table if both the symbol and date exist already in a separate row.

For example if I already have a row for GOOG and today's date then if I try adding a row with that matching information then it either gets denied or updates the existing row with the new information. What is the easiest way to do this?

Upvotes: 0

Views: 1465

Answers (2)

Zak
Zak

Reputation: 461

Take a look at the MERGE statement.

MERGE INTO dbo.StockDailyData AS Target
USING (VALUES ('<your id value>', '<symbol>', '<Market>', '<Open>',
               '<High>', '<Low>', '<Close>', '<Volume>', '<AdjustedClose>', '<Date>')
       AS Source (ID, Symbol, Market, Open, High, Low, Close, Volume, AdjustedClose, Date)
ON Target.Symbol = Source.Symbol AND Target.Date = Source.Date
WHEN MATCHED THEN
    UPDATE SET Target.Market = Source.Market,
       Target.High = Source.High,
       Target.Low = Source.Low,
       Target.Close = Source.Close,
       Target.Volume = Source.Volume,
       Target.AdjustedClose = Source.AdjustedClose,
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Symbol, Market, Open, High, Low, Close, Volume, AdjustedClose, Date) 
       VALUES (Source.ID, Source.Symbol, Source.Market, Source.Open, 
               Source.High, Source.Low, Source.Close, Source.Volume, 
               Source.AdjustedClose, Source.Date

By using MERGE you can set logic for what to do if the record already exists - in your case you would update the new quote information.

Upvotes: 2

otaku
otaku

Reputation: 964

You can use sql triggers to do the pre insert. Check out Trigger to fire only if a condition is met in SQL Server .

Upvotes: 1

Related Questions