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

    Unanswered: Stored Procedure

    I am trying to create an automatic Append Procedure that moves data from one table to the next table. I need to move the TERMINATION. tables data to the Corovan Table after a certain amount of time like 1 year. Can anyone hlep me??

    Code:
    CREATE PROCEDURE [insert_Corovan_Table_1]
    	(@TM_#_1 	[int],
    	 @FirstName_2 	[nvarchar](50),
    	 @LastName_3 	[nvarchar](50),
    	 @SS_#_4 	[nvarchar](50),
    	 @TerminationDate_5 	[datetime],
    	 @Voluntary_or_Involuntary_6 	[nvarchar](30),
    	 @Notes_7 	[nvarchar](75))
    
    AS INSERT INTO [GamingCommissiondb].[dbo].[Corovan_Table] 
    	 ( [TM #],
    	 [FirstName],
    	 [LastName],
    	 [SS #],
    	 [TerminationDate],
    	 [Voluntary or Involuntary],
    	 [Notes]) 
     
    VALUES 
    	( @TM_#_1,
    	 @FirstName_2,
    	 @LastName_3,
    	 @SS_#_4,
    	 @TerminationDate_5,
    	 @Voluntary_or_Involuntary_6,
    	 @Notes_7)
    
    
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Would this be easier?
    Code:
    CREATE PROCEDURE [insert_Corovan_Table_1]
       @pdAsOf		DATETIME = NULL
    AS 
    
    IF @pdAsOf IS NULL
       SET @pdAsOf = DateAdd(year, -1, GetDate())
    
    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 [dbo].[TERMINATION]
       WHERE  terminationDate < @pdAsOf
    
    RETURN
    GO
    -PatP

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

    Yes

    Pat thank you that would be alot easier. I have one more question I forgot to add this, I need to put only some of the data not all of it. (Example) I would need to append the 2003 terms as soon as 2005 approaches.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you take a look at the procedure I posted, it takes a parameter. The sproc will default it to exactly one year ago, but if you specify a value the code will gleefully use whatever date you provide (in the EXECUTE statement).

    You could easily add a second parameter to the procedure, and make it be the end date using similar logic. For example, if you wanted the default values to be one year ago through two years ago, you could use code something like:
    Code:
    CREATE PROCEDURE [insert_Corovan_Table_1]
       @pdBegin		DATETIME = NULL
    ,  @pdEnd		DATETIME = NULL
    AS 
    
    IF @pdBegin IS NULL
       SET @pdBegin = DateAdd(year, -2, GetDate())
    
    IF @pdEnd IS NULL
       SET @pdEnd = DateAdd(year, 1, @pdBegin)
    
    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 [dbo].[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
  •