Results 1 to 6 of 6

Thread: Insert

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

    Unanswered: Insert

    I was informed that this stored procedure is suppose to insert data from one table to another, once a year. Now this part of the statement I understand the rest of it I dont.

    Code:
    INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] 
      ([TM #],
      [FirstName],
      [LastName],
      [SS #],
      [TerminationDate],
      [Voluntary or Involuntary],
      [Notes]) 
    SELECT   ([TM#],  
      [FirstName], 
      [LastName],  
      [SS #],  
      [TerminationDate],  
      [Voluntary_or_Involuntary],
      [Notes],)
    FROM TERMINATION 
    GO


    THIS I'M NOT SURE ABOUT
    Code:
    @pdBegin  DATETIME =  TerminationDate (BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
    @pdEnd    DATETIME=   NULL
    AS
    IF @pdBegin IS NULL
       SET @pdBegin = DateAdd(year, -2, GetDate())
    IF @pdEnd IS NULL
       SET @pdEnd = DateAdd(year, 1,@pdBegin)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Borrowing from your previous posting, I'd use something like:
    Code:
    CREATE PROCEDURE p_Thingie
       @pdBegin  DATETIME =  NULL
    ,  @pdEnd    DATETIME=   NULL
    AS
    
    IF @pdBegin NOT BETWEEN '2003-01-01' AND '2003-12-31'  -- "bad" begin date
       SET @pdBegin = DateAdd(year, -2, GetDate())  -- Back up two years
    
    IF @pdEnd IS NULL  -- If no end date
       SET @pdEnd = DateAdd(year, 1, @pdBegin)  -- One year after the begin date
    Does that help?

    -PatP

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

    Pat

    Hi there pat how are you doing ...Hows life treating you

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Life is going 947 mph, in three directions at once. That is getting to be all too familiar to me! I'm going to be "road tripping" a bunch over the next couple of months... Such fun!

    Did my code-butchering and comments help?

    -PatP

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

    yes

    Yes your sql script helped alot thank you. NOw I just need to have the terminaitons table insert into the corovan table. Combine the two

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh, sorry! I missed the boat on that one thinking that you were Ok with the INSERT. The full tamale would look something like:
    Code:
    CREATE PROCEDURE p_Thingie
       @pdBegin  DATETIME =  NULL
    ,  @pdEnd    DATETIME=   NULL
    AS
    
    IF @pdBegin NOT BETWEEN '2003-01-01' AND '2003-12-31'  -- "bad" begin date
       SET @pdBegin = DateAdd(year, -2, GetDate())  -- Back up two years
    
    IF @pdEnd IS NULL  -- If no end date
       SET @pdEnd = DateAdd(year, 1, @pdBegin)  -- One year after the begin date
    
    INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] (
       [TM #], [FirstName], [LastName]
    ,  [SS #], [TerminationDate], [Voluntary or Involuntary]
    ,  [Notes]
    ) SELECT
       [TM#], [FirstName], [LastName]
    ,  [SS #], [TerminationDate], [Voluntary_or_Involuntary]
    ,  [Notes]
       FROM TERMINATION 
       WHERE  TerminationDate BETWEEN @pdBegin AND @pdEnd
    
    RETURN
    GO
    -PatP

Posting Permissions

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