SQL Server Insert rows returned from table valued function if it does not already exist in the table

myss37ts 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(130)

I'm trying to insert rows returned from a table valued function into a base table only if the id in the row of the returned value does not already exist in the table. The function that I'm cross applying returns a table of IDs based on the input ID.

Here's a simplified version of what I had:

insert into @MainTable 
select FT.ID, FT.Name, FT.ParentID
from ( select T2.ID from @Table2 T2 where T2.UserID = @UserID and...) A
cross apply functionName(A.ID) FT 
where not exists ( select 1 from @MainTable MT where MT.ID = FT.ID )

Table2 might look like this e.g.

ID
100
200

and results from the function would look like this: from function(100)

ID	Name	ParentID
100	A	null
102	B	100
103	C	100
104	D	100

and from function(200)

ID	Name	ParentID
200	E	null
100	A	200
120	F	200

What should be inserted into @MainTable

ID	Name	ParentID
100	A	null
102	B	100
103	C	100
104	D	100
200	E	null
120	F	200

but the duplicate record with ID 100 is inserted anyway - i only care about inserting the first instance returned and ignore any further duplicate IDs even if the parent is different

I thought this would check each row returned by the function before inserting that the id does not already exist in the main table but it seems to ignore that and duplicate IDs still exist in the resulting table. I'm not sure if using distinct would work as I want to keep the row already in the table and discard the new duplicate row only.

It seems like the 'if not exists' check only applies the check to the rows returned from the function once and then inserts them all, but the values returned from the function may contain duplicates so I need it to check if the exists in the table for each row before insertion

sql-server

来源：https://stackoverflow.com/questions/76469752/insert-rows-returned-from-table-valued-function-if-it-does-not-already-exist-in

1条答案

按热度按时间

zzoitvuj1#

It seems like the 'if not exists' check only applies the check to the rows returned from the function once and then inserts them all

Correct, its applying the check before it carries out the insert. If you want to ensure you don't insert further duplicates, you need to check the data you are about to insert.

You can apply a distinct check before you insert using the ROW_NUMBER() method e.g.

with cte1 as (
  select FT.ID, FT.[Name], FT.ParentID
    , row_number() over (partition by ID order by [Name]) RowNum
  from @Table2 A
  cross apply functionName(A.ID) FT 
  where not exists (select 1 from @MainTable MT where MT.ID = FT.ID)
)
insert into @MainTable (Id, [Name], ParentID)
select ID, [Name], ParentID
from cte
where RowNum = 1
group by ID, [Name], PatentID;

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Insert rows returned from table valued function if it does not already exist in the table

1条答案

相关问题

热门标签

最新问答