Avoid duplicate values

  • Hey guys,
    Got this table and I want to avoid inserting the same records.


    CREATE TABLE [dbo].[BR_LIMIT_INSURANCES_TEST_TWO]
    (
     [Branch] [nvarchar](255) NULL,
     [Premium] [varchar](50) NULL,
     [Cases] [money] NULL,
     [ReportMonth] [int] NULL,
     [ReportYear] [int] NULL
    ) ON [PRIMARY]

    Basically, I want to avoid inserting records of the same month. If a month already exists in the table I simply want to update the fields where the duplicate month is, with the values coming in from my table type.

    Here is my stored proc.

    ALTER PROCEDURE [dbo].[BR_INSERT_DATA_FROM_BANK_WORKSHEET]

          @excelTableType ExcelTableType READONLY,
       @month INT,
       @year INT

    AS
    BEGIN
          SET NOCOUNT ON;
          INSERT INTO BR_LIMIT_INSURANCES_TEST_TWO
       (Branch,
        Premium,
        Cases,
        ReportMonth,
        ReportYear)

          SELECT
      Branch,
      Premium,
      Cases,
      @month,
      @year
       FROM @excelTableType
    END

    The values going into @excelTableType are coming from an ASP.NET page from which we can upload Excel worksheets.

    I've tried using an IF EXISTS but it didn't work.
    Thanks in advance

  • Don't know what Premium is as you give no context, so if its important, add it to the query, else:

    IF EXISTS (SELECT 1 FROM BR_LIMIT_INSURANCES_TEST_TWO A INNER JOIN @excelTableType B ON B.Branch = A.Branch AND A.ReportMonth = @month AND A.ReportYear = @year)
    BEGIN
       ---Do Update
    END
    ELSE
    BEGIN
    --Do Insert
    END

  • Rick I'm having trouble writing out the update.

    I basically want to say update SET A.Field = B.Field WHERE A.ReportMonth = @month but I can't get it to work.
    Could you help on the update condition please?

    Thanks

  • Rick-153145 - Tuesday, September 18, 2018 4:18 AM

    Don't know what Premium is as you give no context, so if its important, add it to the query, else:

    IF EXISTS (SELECT 1 FROM BR_LIMIT_INSURANCES_TEST_TWO A INNER JOIN @excelTableType B ON B.Branch = A.Branch AND A.ReportMonth = @month AND A.ReportYear = @year)
    BEGIN
       ---Do Update
    END
    ELSE
    BEGIN
    --Do Insert
    END

    Quick update. 'Premium' is now a MONEY data type column.

  • Rick-153145 - Tuesday, September 18, 2018 4:18 AM

    Don't know what Premium is as you give no context, so if its important, add it to the query, else:

    IF EXISTS (SELECT 1 FROM BR_LIMIT_INSURANCES_TEST_TWO A INNER JOIN @excelTableType B ON B.Branch = A.Branch AND A.ReportMonth = @month AND A.ReportYear = @year)
    BEGIN
       ---Do Update
    END
    ELSE
    BEGIN
    --Do Insert
    END

    On a busy system this will not prevent duplicates
    Read this post from Gail Shaw

    http://source.entelect.co.za/why-is-this-upsert-code-broken

  • DesNorton - Tuesday, September 18, 2018 7:06 AM

    Rick-153145 - Tuesday, September 18, 2018 4:18 AM

    Don't know what Premium is as you give no context, so if its important, add it to the query, else:

    IF EXISTS (SELECT 1 FROM BR_LIMIT_INSURANCES_TEST_TWO A INNER JOIN @excelTableType B ON B.Branch = A.Branch AND A.ReportMonth = @month AND A.ReportYear = @year)
    BEGIN
       ---Do Update
    END
    ELSE
    BEGIN
    --Do Insert
    END

    On a busy system this will not prevent duplicates
    Read this post from Gail Shaw

    http://source.entelect.co.za/why-is-this-upsert-code-broken

    Des is that link for me to read or for Rick?

  • NikosV - Tuesday, September 18, 2018 10:26 AM

    DesNorton - Tuesday, September 18, 2018 7:06 AM

    Rick-153145 - Tuesday, September 18, 2018 4:18 AM

    Don't know what Premium is as you give no context, so if its important, add it to the query, else:

    IF EXISTS (SELECT 1 FROM BR_LIMIT_INSURANCES_TEST_TWO A INNER JOIN @excelTableType B ON B.Branch = A.Branch AND A.ReportMonth = @month AND A.ReportYear = @year)
    BEGIN
       ---Do Update
    END
    ELSE
    BEGIN
    --Do Insert
    END

    On a busy system this will not prevent duplicates
    Read this post from Gail Shaw

    http://source.entelect.co.za/why-is-this-upsert-code-broken

    Des is that link for me to read or for Rick?

    You both could benefit from it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply