creating a custom constraint in sql

Question

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?

possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — PM 77-1, Apr 26 '14 at 01:11

Zak · Accepted Answer · 2014-04-26T02:05:56.763

2

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.

edited Apr 26 '14 at 02:05

answered Apr 26 '14 at 01:28

Zak

461
3
8

This looks exactly like what I need but I'm unsure about how to edit to match my needs. I have hardly worked with databases before. My column names are ID, Symbol, Market, Open, High, Low, Close, Volume, AdjustedClose, and Date and if Symbol and Date both match my code and are in the database already then I want to update that row instead. How would I do this? – Franklin Joseph Moormann Apr 26 '14 at 01:48
If you give me the table names too, I can try to give you a command that will work for you. – Zak Apr 26 '14 at 01:51
It is just one table in my database and it is called dbo.StockDailyData – Franklin Joseph Moormann Apr 26 '14 at 01:55
I hope the update is clear but I suspect you may need to make some adjustments. The values in the "<>" are there for you to replace with your data (from C# variables probably). You might also need to remove references to the 'ID' column if that is set as an IDENTITY column (auto incrementing column...). – Zak Apr 26 '14 at 02:07
Yes the ID column is set as an identity column but I'm really not sure how to place the C# variables in this statement because I was under the assumption that I run the query once on the database and it gets saved or something for all future uses for that table. Does that make sense? I'm inserting data into the table using a tableadapter and a dataset in my C# code – Franklin Joseph Moormann Apr 26 '14 at 02:17
Would I just use the Dataset.merge method instead? – Franklin Joseph Moormann Apr 26 '14 at 02:22
Not sure about that method. This statement is a replacement for using `INSERT` or `UPDATE`. It would definitely be worth checking out Dataset.merge. – Zak Apr 26 '14 at 02:26
So how do I implement this statement with C# since you are saying that it uses C# variables from my code? – Franklin Joseph Moormann Apr 26 '14 at 02:33
Its hard to help without seeing how the data is coming from C# (since you mention its not coming from another table). I imagine you would need to do some string concatenation to build the query. Something like: `SqlCommand myCommand = new SqlCommand("MERGE INTO dbo.StockDailyData AS Target USING (VALUES ('" + id + "','" + symbol + "'...'", conn);` – Zak Apr 26 '14 at 02:44

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

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

edited May 23 '17 at 10:26

Community

1
1

answered Apr 26 '14 at 01:26

otaku

964
5
16

So this is only for mysql? – Franklin Joseph Moormann Apr 26 '14 at 01:49

creating a custom constraint in sql

2 Answers2