Compare Actual table with @TypeTable_TVP IF Duplicate Row Exist

Question

I want to compare Actual table with @TableType_TVP IF Duplicate Row Exist

Create procedure sp_InsertintoTable
@TableType_TVP TableType_TVP READONLY
AS

 IF(Duplicate Row not Exist then)
 Begin 
     INSERT INTO ActualTable
     Select * from @TableType_TVP

      Select Inserted row count also 
 END
else 
  Begin 
        Count Duplicate Row
  End

Are you stuck somewhere or asking for another alternate solution from above pseudo code ? — Krishnraj Rana, Feb 18 '14 at 06:57
Table type is must needed here because from UI Table is returning — Muhammad ADIL, Feb 18 '14 at 07:14

score 1 · Accepted Answer · answered Feb 18 '14 at 07:32

Try this. Note: I have not tested below query in SSMS. So plz point out if any syntax error is there.

CREATE PROCEDURE proc_InsertintoTable 
@TableType_TVP TABLE READONLY
AS
BEGIN
    DECLARE @insertedCnt INT, @dupCnt INT

    -- FETCH DUPLICATE ROW
    SELECT @dupCnt = COUNT(*)
    FROM ActualTable atbl
    WHERE EXISTS (
            SELECT 1
            FROM @TableType_TVP tv
            WHERE atbl.ID = tv.ID
            )

    -- INSERT NEW ROW
    INSERT INTO ActualTable
        SELECT * FROM @TableType_TVP tv
        WHERE NOT EXISTS (
                SELECT 1
                FROM ActualTable atbl
                WHERE atbl.ID = tv.ID
                )

        SELECT @insertedCnt = @@Rowcount

    -- SELECT BOTH VARIABLE
    SELECT @insertedCnt, @dupCnt
    -- OR YOU CAN ALSO SET THIS 2 VARIABLE AS OUT VARIABLE

END

Compare Actual table with @TypeTable_TVP IF Duplicate Row Exist

1 Answers1