September 18, 2018 at 3:51 am
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
September 18, 2018 at 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
September 18, 2018 at 5:08 am
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
September 18, 2018 at 5:24 am
Rick-153145 - Tuesday, September 18, 2018 4:18 AMDon'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.
September 18, 2018 at 7:06 am
Rick-153145 - Tuesday, September 18, 2018 4:18 AMDon'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
September 18, 2018 at 10:26 am
DesNorton - Tuesday, September 18, 2018 7:06 AMRick-153145 - Tuesday, September 18, 2018 4:18 AMDon'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
ENDOn 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?
September 18, 2018 at 12:23 pm
NikosV - Tuesday, September 18, 2018 10:26 AMDesNorton - Tuesday, September 18, 2018 7:06 AMRick-153145 - Tuesday, September 18, 2018 4:18 AMDon'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
ENDOn a busy system this will not prevent duplicates
Read this post from Gail Shaw
http://source.entelect.co.za/why-is-this-upsert-code-brokenDes 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