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

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

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

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

Collectives™ on Stack Overflow

insert in same table avoid duplicate

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related