insert in same table avoid duplicate

Question

Below is my sql statement

INSERT INTO tblRB_ReportFilterUserAssoc (
    [FK_ReportID],
    [UserID],
    [FilterExpression],
    [DateFilterValues],
    [IsCustomReport]
    )
SELECT [FK_ReportID],
    @UserId,
    [FilterExpression],
    [DateFilterValues],
    [IsCustomReport]
FROM tblRB_ReportFilterUserAssoc
WHERE UserID = @AssignedUserID

there is condition of UserID

it is basically inserting all the records in same records of a particular user for another user

i have to check if the particular filter is exist for new user then dont insert

case

how can i do that

[FK_ReportID]  user_id
1                   100
2                   100
3                   100
1                   101

now i want to insert all records of userid 100 to same table for userid 101, but as report id 1 is already there in table for 101 so it should insert only records for 2,3,4

how should we restrict that

Thank You

Which DBMS are you using? You also have very strange formatting rules for your INSERT statements. — user330315
– user330315, Commented Sep 13, 2013 at 6:24

Adriaan Stander · Accepted Answer · 2013-09-13 06:26:49Z

1

How about something like

SELECT 
    [FK_ReportID]
    ,@UserId
    ,[FilterExpression]
    ,[DateFilterValues]
    ,[IsCustomReport]
FROM    tblRB_ReportFilterUserAssoc m
WHERE   UserID = @AssignedUserID
AND     NOT EXISTS (
        SELECT  1 
        FROM    tblRB_ReportFilterUserAssoc s 
        WHERE   m.[FK_ReportID] = s.[FK_ReportID] 
        AND     s.UserID = @UserID
    )

Seeing as you are using SQL Server 2008, have you tried using EXCEPT

Something like

SELECT 
    [FK_ReportID]
    ,@UserId
    ,[FilterExpression]
    ,[DateFilterValues]
    ,[IsCustomReport]
FROM    tblRB_ReportFilterUserAssoc m
WHERE   UserID = @AssignedUserID
EXCEPT
SELECT 
    [FK_ReportID]
    ,@UserId
    ,[FilterExpression]
    ,[DateFilterValues]
    ,[IsCustomReport]
FROM    tblRB_ReportFilterUserAssoc m
WHERE   UserID = @UserId

answered Sep 13, 2013 at 6:26

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rex · Accepted Answer · 2013-09-13 06:29:25Z

1

simply use Except:

INSERT  INTO tblRB_ReportFilterUserAssoc
        ( [FK_ReportID],
          [UserID],
          [FilterExpression],
          [DateFilterValues],
          [IsCustomReport]

        )
        SELECT  [FK_ReportID],
                @UserId,
                [FilterExpression],
                [DateFilterValues],
                [IsCustomReport]
        FROM    tblRB_ReportFilterUserAssoc
        WHERE   UserID = @AssignedUserID
        EXCEPT
        SELECT  [FK_ReportID],
                UserId,
                [FilterExpression],
                [DateFilterValues],
                [IsCustomReport]
        FROM    tblRB_ReportFilterUserAssoc
        WHERE UserID = @UserId

answered Sep 13, 2013 at 6:29

Rex

2,13011 silver badges12 bronze badges

Collectives™ on Stack Overflow

insert in same table avoid duplicate

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related