How to check the table for below mentioned query to avoid the duplicate records insertion.

Question

0.00/5 (No votes)

See more:

I need to insert the record after checking if TariffName is already exists in the table tblCONTSPRateHourTariff or not.

What I have tried:

SQL

insert into tblCONTSPRateHourTariff
  select allotRateHour.Id
  ,(select id FROM [dbo].[tblACCTSPContractTariff] where TariffName='MPS_FLAT_MINIVAN_247' and ContractDetailId=1021)
  ,(select TariffType FROM [dbo].[tblACCTSPContractTariff] where TariffName='MPS_FLAT_MINIVAN_247' and ContractDetailId=1021)
  from tblCONTAllotInventory allot
  join tblCONTSPRateHour allotRateHour on allot.Id=allotRateHour.AllotInventoryId
  --join tblCONTSPRateHourTariff rateHourTariff on allotRateHour.id=rateHourTariff.RateHourId
  join [tblCONTAllotInventSegment] allotSeg on allot.Id=allotSeg.AllotInventoryId
  join tblSYSASegment seg on allotSeg.SegmentId=seg.Id
  join tblCONTServiceProperty prop on allot.SPropertyId=prop.Id
  join tblCONTServiceProvider sp on sp.Id=prop.SPId
  where seg.Name='STORBIL-MPS' and sp.ContractId in (1021)  and seg.SegmentId='ABC123'

Posted 25-Apr-16 21:01pm

Muhammad Islam Manzoor

Updated 25-Apr-16 22:07pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2016-04-25T22:07:00

Solution 1

Add another "where" clause with an "exists" query:

SQL

and not exists (select * from [dbo].[tblACCTSPContractTariff] where TariffName = 'MPS_FLAT_MINIVAN_247')

Posted 25-Apr-16 22:07pm

Andy Lanng

Comments

Muhammad Islam Manzoor 2-May-16 9:01am

Hi,

I Want to store the resultant values in some variable for 1. allotRateHour.Id (Used in select) 2. id (Used in 2nd Select) 3. TariffType (Used in 3rd select) parameters and than want to insert into the table "tblCONTSPRateHourTariff" where i have table columns as (RateHourId, TariffId, Type) after checking the resultant values stored in variable if the values exist in table or not under (RateHourId, TariffId, Type) columns.

Please suggest how to do it?

Andy Lanng 3-May-16 4:06am

I've been off work for a few days. Have you had an answer to this?

Muhammad Islam Manzoor 3-May-16 5:20am

hi, Actually your answer was right for i have asked before. But i have some more queries, if you like to help. I am getting error for below mentioned query.

Begin

Declare @AllotratehoursId int
Declare @Tariffid nvarchar(50)
Declare @TariffType int

IF NOT EXISTS(

Select @AllotratehoursId = allotRateHour.Id
,Select @Tariffid = (id FROM [dbo].[tblACCTSPContractTariff] where TariffName='MPS_FLAT_MINIVAN_247' and ContractDetailId=1021)
,Select @TariffType = (TariffType FROM [dbo].[tblACCTSPContractTariff] where TariffName='MPS_FLAT_MINIVAN_247' and ContractDetailId=1021)
from tblCONTAllotInventory allot
join tblCONTSPRateHour allotRateHour on allot.Id=allotRateHour.AllotInventoryId
--join tblCONTSPRateHourTariff rateHourTariff on allotRateHour.id=rateHourTariff.RateHourId
join [tblCONTAllotInventSegment] allotSeg on allot.Id=allotSeg.AllotInventoryId
join tblSYSASegment seg on allotSeg.SegmentId=seg.Id
join tblCONTServiceProperty prop on allot.SPropertyId=prop.Id
join tblCONTServiceProvider sp on sp.Id=prop.SPId
where seg.Name='STORBIL-MPS' and sp.ContractId in (1021) and seg.SegmentId='SWE001S02'--)

Begin

insert into tblCONTSPRateHourTariff (RateHourId, TariffId, Type)
values ( @AllotratehoursId,@Tariffid,@TariffType)

End

End

But i am getting errors while executing above.

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'from'.

Please help and thanks in advance.

Andy Lanng 3-May-16 6:09am

Ah - I see the problem.
There are two types of select query. The value select is a plain old select query but a variable select is what you use to populate the variables.

In a "Exists" clause you must use the value select query. The "Exists" clause evaluates if any results are returned from the query is evaluates. Variable selects do not return values in favour of populating the variables instead and so cannot be used in the "Exists" clause

If you want more help then you will have to post a new question. It's not very easy to respond in comments alone. You can refer to this question in your new one if you need to.

Muhammad Islam Manzoor 3-May-16 8:54am

thanks for reply.Means i have to post one more question.

Andy Lanng 3-May-16 11:22am

I'll keep an eye out for it ^_^