Results 1 to 5 of 5

Thread: Insert Statment

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Insert Statment

    Hi all when I originally did my insert I forgot some fields and now I need to insert them but when I executed my statement I got 0 rows returned. Am I doing something wrong??


    Code:
    INSERT INTO People_tbl
                          ([Type Participant], ServiceArea, ReferralStatus)
    SELECT     [Type Participant], [Service Area], [Referral Status]
    FROM         Parent_Sc
    WHERE     (NOT EXISTS
                              (SELECT   [Type Participant], [Service Area], [Referral Status]
                                FROM          People_tbl
                                WHERE      (People_tbl.[Parent ID] = Parent_sc.[Parent ID])))

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The "WHERE (NOT EXISTS" in your SQL-script prevents the INSERT of records that already exist. That is the reason why you get 0 records inserted, they already are there, but with some columns empty (NULL).

    What you need is an UPDATE statement that will update the fields (columns) you forgot to include.

    Something in the line of
    Code:
    UPDATE People_tbl
    SET forgotten_field1 = Parent_Sc.forgotten_field1,
        forgotten_field2 = Parent_Sc.forgotten_field2,  
        forgotten_field3 = Parent_Sc.forgotten_field3
    FROM Parent_Sc
    WHERE People_tbl.[Parent ID] = Parent_sc.[Parent ID] AND
        this_record_was_inserted_recently
    The challenge for you will be to find out which records must be udpated (the new ones) and which may not (those that already existed when you first ran your INSERT script). Perhaps it's sufficient to include in the WHERE clause "forgotten_field1 IS NULL OR forgotten_field2 IS NULL OR forgotten_field3 IS NULL "
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well shoot I tired the update statement and since I did it wrong automatically I thought I was doing it wrong and went to an insert statement. Ok My bad thank you Wim. Bad Assumption on my part

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok so how is this then??

    Code:
    UPDATE    People_tbl
    SET              [Week] = Parent_Sc.[Weekly Hrs], [Month] = Parent_Sc.[Mo Hours], ServiceArea = Parent_Sc.[Service Area], ReferralStatus = Parent_Sc.[Referral Status]
    
    From Parent_Sc
    WHERE People_tbl.[Parent ID] = Parent_Sc.[Parent ID]

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It will work.

    But with this script you will update ALL the People_tbl records with a matching Parent_Sc record. Are you sure this is correct?

    It will overwrite the data already present in the table People_tbl, you will be facing data loss if the old data had to be preserved.

    This script gives you all the records that will be altered by your UPDATE script:
    Code:
    SELECT People_tbl.[Parent ID] 
        [Week], Parent_Sc.[Weekly Hrs],
        [Month], Parent_Sc.[Mo Hours], 
        ServiceArea, Parent_Sc.[Service Area],
        ReferralStatus, Parent_Sc.[Referral Status]
    FROM People_tbl
         INNER JOIN Parent_Sc ON 
           People_tbl.[Parent ID] = Parent_Sc.[Parent ID]
    WHERE NOT (COALESCE([Week], -1) = COALESCE(Parent_Sc.[Weekly Hrs], -1) AND
     COALESCE([Month], -1) = COALESCE(Parent_Sc.[Mo Hours], -1) AND
     COALESCE(ServiceArea, '&') = COALESCE(Parent_Sc.[Service Area], '&' AND
     COALESCE(ReferralStatus, '&' = COALESCE(Parent_Sc.[Referral Status], '&'))
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •