I tried to simplify what I am trying to do. This is for work, we sell metal. When an order is placed material, coating, masking, etc. is chosen. Then someone allocates material for the order. They want to create 2 programs. One where a user will enter in an order number, it will verify it is a correct order number. It will then look at 4 tables with existing data. It needs to take certain specs requested from those 4 tables and put them in 4 other tables that are currently empty. Bottom line is they want to find out what was allocated for the order. If that particular combination has already been requested on a different order number they just want to add a 1 to times_used column. End result would be a different program that looks through these new tables. They want to be able to determine what needs to be allocated for an order by looking at what was previously allocated on other orders. Is there any way this can be one program and not two? The information is already in the tables I don' know why I couldn't just do this with 1 program.
This is where views come in handy, and an understanding of relational databases. You can store each entry in a table dedicated for that. Each entry can have its own PK value that lets you differentiate between it. However, each entry can refer to a separate table to identify the Customer, Order, LineSeq. If you need to insert an entry for a Cus+Ord+LS that doesn’t exist yet, you create a new one, with its own PK value. Then you create a view which joins those two tables, including a
COUNT_BIG(*) aggregate to count how many times they’re used. You could even index the view so that the values are worked out ahead of time.
To use this the way you want, you could put an “instead of trigger” on your view that handles the inserts/deletes into your entries table. That’s essentially a procedure that you interact with by doing inserts into a view, even though your view can’t handle inserts.
You need to get your app to do it instead of your SQL Query, but you need some way of monitoring race conditions (is there 1 copy of the app running at a time?)
If this is some sort of load process that is running single threaded, then you could select from the table on what needs to be unique. If no results are returned from the select then you run an insert statement:
insert into table 1 (column list) values (initial values)
If a result is returned from the select statement, then you need to run an update statement:
update table_1 set times_used = times_used + 1 where [whatever makes it unique]
You could do this in a SQL Query with an IF EXISTS (Select statement).
The problem comes when you're running this from multiple sources. You'll need some sort of lock so that if 2 processes check if the record exists at the same time, they don't both try to insert the record.
Based on the information available at this time:
First, it looks like you're using
id_no as a surrogate key for
allocated_dt. In other words, there will only be one row in
Table_1 for any specified set of values in those four columns.
To enforce the uniqueness of those 4 columns, I'd recommend creating a unique index on them. This will guarantee that you can't have two rows with the same values in those four columns.
That said, the primary key on
Table_1 will be
id_no, and that should be a auto-increment columns (handled in SQL Server by making it an
Since whenever a new row is added to
Table_1 the value of
times_used should be 1, we can set a
DEFAULT constraint for that column.
CREATE TABLE statement for
Table_1 will look something like this:
CREATE TABLE Table_1 (id_no int IDENTITY(1,1) NOT NULL, Cus_no int, Ord_no int, Line_seq_no int, allocated_dt DATE, times_used int CONSTRAINT DF_Table_1_times_used DEFAULT (1), CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (id_no), CONSTRAINT UX_Table_1__Cus_Ord_LineSeq_AllocDt UNIQUE NONCLUSTERED (Cus_no, Ord_no, Line_seq_no, allocated_dt) );
That lays the groundwork. You may need to adjust to make sure the data types are all correct. Also, if any of the four fields (other than
times_used) would not be part of the check, then you'd need to adjust the unique index.
Now, for the actual
This looks like a perfect place to use the
MERGE statement (a sentence I never expected to utter).
So, if there's already a row with the
allocated_dt, you just want to update that row by adding 1 to the
times_used value. If there isn't, you want to insert the data, setting the
id_no to the next
IDENTITY value for the table, and with
times_used set to 1.
Our table definition takes care of the
INSERT, so we can ignore that part.
We'll assume that the
Ord_no to find in
Table_3 will be in a variable,
MERGE statement would look like this:
MERGE INTO Table_1 as t1 USING (SELECT t2.Cus_no, t3.Ord_no, t3.Line_seq_no, t3.allocated_dt FROM Table_2 as t2 INNER JOIN Table_3 as t3 ON t2.Ord_No = t3.Ord_No WHERE t2.Ord_no = @Ord_no) as source ON (t1.Cus_no = source.Cus_no AND t1.Ord_no = source.Ord_no AND t1.Line_seq_no = source.Line_seq_no AND t1.allocated_dt = source.allocated_dt) WHEN MATCHED THEN UPDATE SET times_used = t1.times_used + 1 WHEN NOT MATCHED THEN INSERT (Cus_no, Ord_no, Line_seq_no, allocated_dt) VALUES (source.Cus_no, source.Ord_no, source.Line_seq_no, source.allocated_dt) ;
Basically, what this means - is exactly what we said in the paragraph above.
You can see this working (and generating the results you expect) at this dbfiddle.uk link. Note that I did add values for
Line_seq_no. Also, that this is running in SQL Server 2012 - that's the oldest version I could find online right now. However, the
MERGE statement is available in SQL Server 2008, so you should be OK.
Also note: I'm taking what you said at face value, as far as doing this for work. If this is for a class, if I were you, I wouldn't use
MERGE, unless they've been talking about it in class. You can do the same thing by breaking things down into separate steps: check if the row already exists in
UPDATE if it does,
INSERT if it doesn't.