Iterating through a TVP before inserting records?

Question

I'd like some help writing the following sproc:

I have SQL Server 2008 sproc that accepts two integer values (@ID1 and @ID2) and a data table/TVP.

The TVP table contains several fields, ie. Title and Description.

I want to iterate through the TVP table and check if the Title or Description already exists in my data table, tbl_Items, where @ID1 = tbl_Items.ID1 and @ID2 = tbl_Items.ID2.

If neither exist then insert the values of @ID1 and ID2 and that TVP row into tbl_Items.

Thanks.

score 1 · Accepted Answer · answered May 12 '11 at 23:24

1

Something like this?

INSERT INTO tbl_Items (ID1, ID2, Title, Description)
  SELECT
    @ID1, @ID2, TVP.Title, TVP.Description
  FROM
    @TVP AS TVP
  WHERE
    NOT EXISTS (SELECT * FROM tbl_Items AS I WHERE TVP.Title = I.Title AND TVP.Description = I.Description)

answered May 12 '11 at 23:24

This did the trick and was as simple as I figured it should be, thank you. I was afraid that I may have to create a CTE, as listed above. – ElHaix May 14 '11 at 16:10

score 0 · Answer 2 · answered May 12 '11 at 23:43

The requirement seems somewhat unclear but you should be able to use MERGE

;WITH Target As
(
SELECT * 
FROM tbl_Items 
WHERE ID1=@ID1 AND ID2=@ID2
)
MERGE 
    INTO Target
    USING @TVP AS Source
    ON Target.Title = Source.Title OR Target.Description = Source.Description
    WHEN NOT MATCHED 
        THEN INSERT  (ID1, ID2, Title, Description) 
              VALUES (@ID1, @ID2, Title, Description)

Iterating through a TVP before inserting records?

2 Answers2