SQL Server Insert Using View

Question

This is a simplified example of what I want to do. Assume there is table named contractor that looks like this:

name | paid_adjustment_amount | adj_date
Bob  | 1000                   | 4/7/2016
Mary | 2000                   | 4/8/2016
Bill | 5000                   | 4/8/2016
Mary | 4000                   | 4/10/2016
Bill | (1000)                 | 4/12/2016
Ann  | 3000                   | 4/30/2016

There is a view of the contractor table, let's call it v_sum, that is just a SUM of the paid_adustment_amount grouped by name. So it looks like this:

name | total_paid_amount
Bob  | 1000
Mary | 6000
Bill | 4000
Ann  | 3000

Finally, there is another table called to_date_payment that looks like this:

name | paid_to_date_amount
Bob  | 1000
Mary | 8000
Bill | 3000
Ann  | 3000
Joe  | 4000      

I want to compare the information in the to_date_payment table to the v_sum view and insert a new row in the contractor table to show an adjustment. Something like this:

INSERT INTO contractor
SELECT to_date_payment.name, 
    to_date_payment.paid_to_date_amount - v_sum.total_paid_amount,
    GETDATE()
FROM to_date_payment
LEFT JOIN v_sum ON to_date_payment.name = v_sum.name
WHERE to_date_payment.paid_to_date_amount - v_sum.total_paid_amount <> 0
    OR v_sum.name IS NULL

Are there any issues with using a view for this? My understanding, please correct me if I'm wrong, is that a view is just a result set of a query. And, since the view is of the table I'm inserting new records into, I'm afraid there could be data integrity problems.

Thanks for the help!

Answer 1

In order to fully understand what you are doing, you should also provide the definition for v_sum. Generally speaking, views might provide some advantages, especially when indexed. More details can be found here and here.

Simple usage of views do not provide performance benefits, but they are very good of providing abstraction over tables.

In your particular case, I do not see any problem in JOINing with the view, but I would worry about potential problems related to:

1) JOIN using VARCHARs instead of integer - ON to_date_payment.name = v_sum.name - if possible, try to JOIN on integer (ids or foreign keys ids) values, as it is faster (indexes applied on integer columns will have a smaller key, comparisons are slightly faster).

2) OR in queries - usually leads to performance problems. One thing to try is to change the SELECT like this:

SELECT to_date_payment.name, 
    to_date_payment.paid_to_date_amount - v_sum.total_paid_amount,
    GETDATE()
FROM to_date_payment
JOIN v_sum ON to_date_payment.name = v_sum.name
WHERE to_date_payment.paid_to_date_amount - v_sum.total_paid_amount <> 0

UNION ALL

SELECT to_date_payment.name, 
    to_date_payment.paid_to_date_amount,    -- or NULL if this is really intended
    GETDATE()
FROM to_date_payment
-- NOT EXISTS is usually faster than LEFT JOIN ... IS NULL
WHERE NOT EXISTS (SELECT 1 FROM v_sum V WHERE V.name = to_date_payment.name)

3) Possible undesired result - by default, arithmetic involving NULL returns NULL. When there is no match in v_sum, then v_sum.total_paid_amount is NULL and to_date_payment.paid_to_date_amount - v_sum.total_paid_amount will evaluate to NULL. Is this correct? Maybe to_date_payment.paid_to_date_amount - ISNULL(v_sum.total_paid_amount, 0) is intended.