Post reply

Insert Row Problem

  • October 15, 2008 at 3:28 am

    #73059

    Hi

    I have 2 tables. [CONTAINERS] AND [INDEXDATA].

    THEY JOIN ON CONTAINERS.CONTAINERID = INDEXDATA.CONTAINERID

    [CONTAINERS] contain only unique container codes and [INDEXDATA] can contain many records for each container.

    I'm trying to create a T-SQL stement that can run daily to check if each container in [CONTAINERS] has an entry in [INDEXDATA].INDEXFIELD1 called '*** FULL CONTENT ***'. If not, I want to insert 1 entry in [INDEXDATA] for each [CONTAINERS].CONTAINERID.

    In other words, there must always be 1 and only 1 entry in [INDEXDATA].INDEXFIELD1 called '*** FULL CONTENT ***' for each container in [CONTAINERS].

    Here's what I have but it's not working....

    SELECT CONTAINERS.CONTAINERID, '*** FULL CONTENT ***', 7, CONTAINERS.CONTAINERORIGINID, 1, CONTAINERS.COMPANYID, CONTAINERS.SUBCOID, 1,

    CONTAINERS.DEPT, CONTAINERS.LEVELID, 1

    FROM CONTAINERS INNER JOIN

    INDEXDATA ON CONTAINERS.CONTAINERID = INDEXDATA.CONTAINERID

    HAVING SUM(CASE WHEN INDEXFIELD1 = '*** FULL CONTENT ***' THEN 1 ELSE 0 END) = 0

    GROUP BY CONTAINERS.CONTAINERID, CONTAINERS.CONTAINERORIGINID, CONTAINERS.COMPANYID, CONTAINERS.SUBCOID, CONTAINERS.DEPT,

    CONTAINERS.LEVELID

  • October 15, 2008 at 3:54 am

    #885095

    Try this, barcode:

    [font="Courier New"]SELECT c.CONTAINERID, '*** FULL CONTENT ***', 7, c.CONTAINERORIGINID, 1, c.COMPANYID, c.SUBCOID, 1, c.DEPT, c.LEVELID, 1

    FROM CONTAINERS c

    LEFT JOIN INDEXDATA i ON i.CONTAINERID = c.CONTAINERID AND i.INDEXFIELD1 = '*** FULL CONTENT ***'

    WHERE i.CONTAINERID IS NULL

    [/font]

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 15, 2008 at 4:04 am

    #885102

    🙂 Thanks Chris, that worked like a dream!

  • October 15, 2008 at 4:24 am

    #885109

    You're welcome, barcode, many thanks for the feedback.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply