Stored Procedure SQL Server 2000


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Stored Procedure SQL Server 2000

Hybrid View

  1. #1
    Join Date
    Dec 2003
    Location
    Italy
    Posts
    250

    Unhappy Stored Procedure SQL Server 2000

    Hello guys,
    I have to create a stored procedure that generates a recordset and fill a table with the data retrieved by the stored procedure.
    The table has a key with 2 fields, employee number and a process date.
    The Stored procedure must be executed every month and the table can store more than one record per employee, but I can't store duplicated data for the same employee. for example :

    on May 2005, The stored procedure find something like this :

    employee_num Process_Date Field1 Field2 Field3....
    1 05/31/2005 A B C

    On June 2005, The Stored Procedure don't find me, so no record for me in that month

    On July 2005, The Stored Procedure find me again with this data :

    employee_num Process_Date Field1 Field2 Field3....
    1 07/31/2005 D E F

    I should see this information in the table :

    employee_num Process_Date Field1 Field2 Field3....
    1 05/31/2005 A B C
    1 07/31/2005 D E F

    But it's not possible to have something like this :

    employee_num Process_Date Field1 Field2 Field3....
    1 05/31/2005 A B C
    1 05/31/2005 A B C

    The problem is that if the stored procedure finds a duplicate record i get the next error message :

    Server: Msg 2627, Level 14, State 1, Line 1
    Violation of PRIMARY KEY constraint 'PK_TABLE_NAME'. Cannot insert duplicate key in object 'TABLE_NAME'.
    The statement has been terminated.

    The SQL Statament I used to insert the records into the table is something like this :

    INSERT INTO TABLE_NAME

    SELECT

    Employe_Num
    Process_Date
    Field1
    Field2
    Field3

    FROM

    .....


    The problem is that it doesn't inserts the records that MUST be inserted into the table either because if the statement finds a duplicate key, it stops all the process.
    I'be been thinking about creating a VBP and scroll all the recordset inserting into the table only the records I need, but I want to do it directly in SQL Server.
    How can I solve this problem in Transact SQL ? (Stored Procedures, Triggers, etc)

    Thank you all

  2. #2
    Join Date
    Dec 2003
    Posts
    78
    Your Select should look something like this

    SELECT Table1.ColumnA, Table1.ColumnB
    FROM Table1 LEFT OUTER JOIN
    Table2 ON Table1.ColumnA = Table2.ColumnA AND Table1.ColumnB = Table2.ColumnB
    WHERE Table2.ColumnA IS NULL

    The Key part is join TableA to TableB with as many columns that are required to be unique.

  3. #3
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    If the problem is that you are getting a duplicate during the select, you could also use the DISTINCT keyword.

    If the problem is that the record already exists and you are doing a select, then you can filter them out in the select. There are two ways to do this:

    1) Destination table is small

    Code:
    INSERT INTO TABLE_NAME
    SELECT 
    Employe_Num
    Process_Date
    Field1
    Field2
    Field3
    
    FROM
    Table2 
    
    where (Employe_Num+Process_Date) Not in (Select Employe_Num+Process_Date) from Table2
    The problem occurs when the "Not In" set becomes large. Another approach is to use a cursor...


    2) when the dest table is large:

    Code:
    ' Must declare the variables for every field
    Declare @Employe_Num Varchar(10),
               @Process_Date varchar(10),.... etc...
    
    ' Declare Count var
    Declare @Count tinyint
    
    ' Create the cursor
    Declare cSource CURSOR For
    SELECT 
    Employe_Num
    Process_Date
    Field1
    Field2
    Field3
    
    FROM
    Table2 
    
    ' Open the cursor
    Open cSource
    Fetch NEXT from cSource into @Employe_Num, @Process_Date, @Field1, @Field2, @Field3
    
    ' Loop through the cursor
    While (@@FEATCH_STATUS=0)
        BEGIN
    
    	' See if the record exists in the destination table
    	Select @Count = Count(*) 
    	from Table2 
    	where Employe_Num = @Employe_Num
    	And Process_Date = @Process_Date
    
    	' If none found, then do the insertion
    	If @Count = 0
    		Insert into TABLE_NAME(Employe_Num, Process_Date, Field1, Field2, Field3)
    		Values(@Employe_Num, @Process_Date, @Field1, @Field2, @Field3)
    
    	' Get next record	
    	Fetch NEXT from cSource into @Employe_Num, @Process_Date, @Field1, @Field2, @Field3		
        END
    
    ' close and dispose of the cursor
    Close cSource
    Deallocate cSource
    Last edited by brouse; 03-17-2005 at 11:09 AM.
    Bob Rouse
    Dimension Data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center