Wednesday, November 16, 2011

How to avoid duplicate insertion of records in sql

The redundancy in database occur due to duplication records insertion.For this Here i have shown a simple stored procedure to prevent  the duplicate insertion of records  into database table.

Stored Procedure:
USE [DBname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TableName]
    @Month [nvarchar](50),
    @Name[nvarchar](max),
    @ordr [nvarchar](max),
    @Region [nvarchar](50),
    @Country [nvarchar](50),
    @Col_Id [int],
    @Order_id [int],
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    IF  NOT EXISTS(SELECT * FROM [dbo].[Dfp_Main_Data] WHERE
      [Month] = @Month AND
      [Name] = @NameAND
      [ordr] = @ordr AND
      [Region] = @Region AND
      [Country] = @Country AND
      [Col_Id] = @Col_Id AND
      [Order_id] = @Order_id AND
   )   
        BEGIN
         INSERT INTO [dbo].[Dfp_Main_Data]
           ([Month]
           ,[Col]
           ,[ordr]
           ,[Region]
           ,[Country]
           ,[Col_Id]
           ,[Order_id]
           )
         VALUES
           (@Month
           ,@Col
           ,@ordr
           ,@Region
           ,@Country
           ,@Col_Id
           ,@Order_id
          )
         END
        END

No comments:

Bel