Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: AppendStored Procedure

    I got this stored procedure I'm running that inserts data from one table to another, but it keeps telling me that I'm violating the primary key rule which means one those records or 12 for all I know, already exsist in the other table. How do I add a IF ALREADY EXSIST CLAUSE TO MY STORED PROCEDURE, is that the correct word for it?? I'll look it up also. Heres the stored procedure I have so far.

    Code:
    ALTER  PROCEDURE dbo.[2003_CorovanInsert]
    AS INSERT INTO dbo.Corovan_Table
                          ([TM #], FirstName, LastName, [SS #], TerminationDate)
    SELECT     [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
    FROM         dbo.[2003 TERMINATIONS]
    WHERE     (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You are almost there. Try this (it will need a little bit of work from you, but you should get it quickly)

    Code:
    ALTER  PROCEDURE dbo.[2003_CorovanInsert]
    AS INSERT INTO dbo.Corovan_Table
                          ([TM #], FirstName, LastName, [SS #], TerminationDate)
    SELECT     [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
    FROM         dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
    WHERE     (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
      and dbo.corovan_table.(primary key field) is null
    Go
    You will have to supply the (primary key field)'s. Hope this helps.

  3. #3
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    ALTER PROCEDURE dbo.[2003_CorovanInsert]
    AS INSERT INTO dbo.Corovan_Table
    ([TM #], FirstName, LastName, [SS #], TerminationDate)
    SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
    FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
    WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
    and not exists (select <primary key field> from dbo.corovan_table)Go
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

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

    Hooray

    Problem solved guys thanks so much for your help, I sure do appreciate it, was struggling with that all day. At least it was a nice learning experience for me.

    Thanks guys

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

    Help Again

    Hi guys its me again I'm having problems with my stored procedure again it executes as long as theres no primary key rule violations. But there is a violation Of the primary key because the the stored procedure was executed yesterday, but today I have discovered that since this is based on the 2003 terms date not all the 2003 terms were dated as 2003 (user error) so now I need to edit the stored procedure, which of course I'm not doing correctly.

    Help??? Again Please

    Code:
    ALTER PROCEDURE dbo.[03Corovans]
    AS INSERT INTO dbo.Corovan_Table
                          ([TM #], FirstName, LastName, [SS #], TerminationDate)
    SELECT     [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
    FROM         dbo.[03TermsWithoutMatchingCorovan]
      and not exists (select <TM #> from dbo.Corovan_table)Go

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

    Why Do I Even Bother

    Oh good Grief Never mind I looked at the script again and did the necessary corrections now it works just fine. AND I FEEL LIKE AN IDIOT

    SORRY GUYS

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

    Red face Embarrassed

    I'm so ashamed and embarrassed......DUH!!!!!!!!!!!!!!!!!!!!!!

    Merry Christmas and
    Happy Holidays everyone

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    AND I FEEL LIKE AN IDIOT
    Does that mean "with your hands" ???

    -PatP

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

    No

    That means with my hands and My SO CALLED BRAIN

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

    Pat

    Did that answer your question Pat or did I miss the point??

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    Did that answer your question Pat or did I miss the point??
    You are assuming that my questions have a point... Not everyone would jump on that bus!

    Yes, an idiot normally feels things with their hands, so if you are "feeling like an idiot", I'd assume that you were wandering about with a goofy expression, putting your hands on everything you could.

    Nevermind... I never claimed that it made any sense, I just found it humorous!

    -PatP

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

    HiGH IQ's

    You know Pat I know alot of people with very High IQ's who have a simular sense of humor as yourself, so I should have known better. Next time I'll just let that bus go right by me.
    Last edited by desireemm; 12-17-04 at 15:56.

Posting Permissions

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