Avoid insert duplicate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Avoid insert duplicate

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2014-05-27 : 04:41:31

hi
Sorry,in order to avoid spam. I have deleted the previous post.

Here is my code and table sample. In the 5th and 6th row of Table C are actually the duplicate of 1st and 2nd row which i want to avoid. How should i rewrite the SQL to avoid duplicate insertion? Thanks a lot.

Select @ID = Max(ID) + 1 from TableC

With Cte as
(
Select distinct TableB.ProdID TableB where TableB.ProdGroupId = @GroupID
)
Insert into [dbo].[TableC]([ID] ,[TEXT], [ProdGroupId])
Select @ID, @Text, TableB.ProdGroupId from TableB INNER JOIN CTE
ON TableB.SupplierProdID = Cte.ProdID
where TableB.ProdID = @ProdID

Table C
1 A1 Grp1
2 A2 Grp2
2 A3 Grp1
3 A4 Grp2
3 A1 Grp1
4 A2 Grp2

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-05-27 : 05:25:11

Insert into [dbo].[TableC]([TEXT], [ProdGroupId])
Select distinct @Text, TableB.ProdGroupId from TableB INNER JOIN CTE
ON TableB.SupplierProdID = Cte.ProdID
where TableB.ProdID = @ProdID

update [TableC]
set [ID] = @ID...your where logic

Hema Sunder

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-27 : 08:41:30

I'd try this using merge. However, Since I don't know what's in TableB, I can't really work up an example for you. Could you please provide some data for Table B and your expected results.

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-05-27 : 09:36:57

Can you try this:

With Cte as
(
Select distinct TableB.ProdID TableB where TableB.ProdGroupId = @GroupID
),
CTE2
AS
(
Select @Text AS [Text], TableB.ProdGroupId, ROW_NUMBER()OVER(PARTITION BY @Text, TableB.ProdGroupId ORDER BY TableB.ProdGroupId) AS ROWNUM
from TableB INNER JOIN CTE
ON TableB.SupplierProdID = Cte.ProdID
where TableB.ProdID = @ProdID
)

Insert into [dbo].[TableC]([ID] ,[TEXT], [ProdGroupId])
Select @ID, [Text], ProdGroupId
FROM CTE2
where ROWNUM=1

--------------------
Rock n Roll with SQL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 11:36:36

Put a Unique Constraint on those two columns.

http://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

EDIT: Opps I mis-read what you wanted. Please ignore.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2014-05-30 : 05:45:18

Thanks everybody. Been busy and did not have time to reply. I used this to delete duplicate. Thanks a lot.

with cte as(
select row_number() over (partition by Prod_ID, ProdName
order by ID desc) as rn
from prodduct where Prod_ID = @Prod_ID)
delete from cte
where rn > 1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-30 : 07:38:52

better to avoid inserting the duplicate in the first place!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources