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.
-
1Can you explain the point? What happens if you insert row 1, then insert row 2, then delete row 1?– Aaron BertrandSep 13, 2018 at 20:24
-
1This kind of per-thing sequence is almost never worth the trouble it is to implement and meet everyone's expectations. Everything is a nightmare, from locking to error handling to existence checking to the arbitrary need for it to be gapless. Just say no.– Erik DarlingSep 13, 2018 at 20:30
3 Answers
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 Cus_no
, Ord_no
, Line_seq_no
, and 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 IDENTITY
column.
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.
So, the 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 id_no
and times_used
) would not be part of the check, then you'd need to adjust the unique index.
Now, for the actual INSERT
or UPDATE
:
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 Cus_no
, Ord_no
, Line_seq_no
, and 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 id_no
and times_used
on INSERT
, so we can ignore that part.
We'll assume that the Ord_no
to find in Table_2
and Table_3
will be in a variable, @Ord_no
.
The 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 Table_1
, UPDATE
if it does, INSERT
if it doesn't.