Follow-up question SQL Server Append query

Posted on 2014-08-21
Last Modified: 2014-08-22
Background is in this question.

Now I need to do an insert into that same table for those records from my view that do not have a matching record in the destination table, based on Equip_ID and DT_Recorded/docDate.  The query I have looks like:

USE WHR_System_Tables

DECLARE @AsOf as datetime2(7)


INSERT INTO tbl_Readings_Tanks (Equip_ID, docDate, Prod_ID, Inches_End, Closing_Vol)
SELECT T.Equip_ID, T.DT_Recorded, T.Product_ID, T.Inches, T.Closing_Vol  
, CTRI.FacilityID
, CTRI.DT_Recorded
, CTRI.Product_ID
, CTRI.TankNum
, CTRI.Inches
, ET.Diameter
, Closing_Vol = (CTRI.Inches/12) * (3.14159 * (ET.Diameter/2) * (ET.Diameter/2)) * 0.17811
FROM  dbo.vw_Equipment_Tanks as ET
INNER JOIN dbo.vw_Cygnet_Tank_Readings_Inches as CTRI
ON ET.DS_PK_Text = CTRI.FacilityID 
AND ET.Product_ID = CTRI.Product_ID 
AND ET.Tank_Num = CTRI.TankNum
WHERE (@AsOf IS NULL) OR (CTRI.Dt_recorded > @AsOf)
) as T
LEFT JOIN tbl_Readings_Tanks as RT
ON T.Equip_ID = RT.Equip_ID
AND T.DT_Recorded = RT.docDate

Open in new window

The select part of this is basically an unmatched query, find all the records in T, that don't have a matching record in RT, and insert them into tbl_Readings_Tanks.
Question by:Dale Fye (Access MVP)
    LVL 47

    Author Comment

    by:Dale Fye (Access MVP)
    At the moment, because @AsOf = NULL, there are about 10,000 records that will be added to the destination table, which contains about 2M records at the moment.

    That query is still running after over 15 minutes.  So the real question is, is there a quicker way to identify the records in T that don't already exist in RT, something like:

    SELECT * FROM (...) as T
    WHERE NOT Exists (SELECT TankReadingID FROM tbl_Readings_Tanks as RT where RT.Equip_ID = T.Equip_ID and RT.docDate = T.DT_Recorded)

    Is that going to run any quicker?  I've already got an index on Equip_ID and docDate in tbl_Readings_Tanks.
    LVL 47

    Expert Comment

    I'm not certain what the question is to be honest. Is there a problem in the above?


    Since sql 2008 there is a MERGE capability. With this you can combine both the update and insert (sometimes referred to as "upsert" :)

    The structure of a MERGE is neat. You can branch on "WHEN MATCHED" where you do the updating, and WHEN NOT MATCHED" where you do the inserts, and you then don't need to concern yourself with determining the un-matched records - merge does this for you.
    LVL 47

    Author Comment

    by:Dale Fye (Access MVP)

    The "problem" with the above is that it took 17 min, 33 seconds to append 9K records.  Would like to do this significantly quicker!  I'll take a look at the Merge syntax, we have been able to do "Upsert" queries in Access since at least 2003, but last time I had checked, SQL Server didn't have that capability.  Is it basically the same as the Update query but with left joins between the table that has the data and the destination table?

    LVL 47

    Accepted Solution

    oh, I see now, sorry, your follow-up wasn't available as I was prepping my initial reply.

    SELECT * FROM (...) as T
    WHERE NOT Exists (SELECT TankReadingID FROM tbl_Readings_Tanks as RT where RT.Equip_ID = T.Equip_ID and RT.docDate = T.DT_Recorded)

    Is that going to run any quicker?  

    I would say "yes, good chance it will be faster, but can't guarantee it."

    It isn't necessary to reference a field in the select clause, this would do the same job:

    SELECT * FROM (...) as T
    WHERE NOT Exists (SELECT NULL FROM tbl_Readings_Tanks as RT where RT.Equip_ID = T.Equip_ID and RT.docDate = T.DT_Recorded)

    or: select 1

    the exists/not exists decision is done inside the from clause, that select clause just goes for the ride :)
    LVL 47

    Author Closing Comment

    by:Dale Fye (Access MVP)
    Well, the Not Exists method only took 11 min, 33 sec, as opposed to over 17 minutes for the other method.

    am amazed that there is not a quicker method, but if I can combine the matched and unmatched queries with a merge, that might be even better.
    LVL 47

    Expert Comment

    Thanks. Still slow though :(
    are statistics and indexes in good shape?
    I suggest then looking at the execution plan

    Cheers, Paul

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now