Avoid duplicate values

Question

Avoid duplicate values

NikosV

SSCrazy

Points: 2111
More actions
September 18, 2018 at 3:51 am

#379185

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

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

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

Rick-153145 SSCrazy Points: 2823 More actions · Answer 1

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

NikosV SSCrazy Points: 2111 More actions · Answer 2

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

NikosV SSCrazy Points: 2111 More actions · Answer 3

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.

DesNorton SSC-Insane Points: 24387 More actions · Answer 4

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

How to post data/code on a forum to get the best help.

NikosV SSCrazy Points: 2111 More actions · Answer 5

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?

DesNorton SSC-Insane Points: 24387 More actions · Answer 6

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.

How to post data/code on a forum to get the best help.