I have two main tables
If the row in position intersects the row in geography, I need to insert it in third table.
But the problem is I have to check the existing row in the third table and then insert it.
I can do this with set operations,but the problem is I can't process each row (i.e. if there are two rows with similar data,need to insert only the first).
I can do with cursor but I read its a bad thing.
Can anyone help me to process each row before inserting?
At first put all you need in CTE with ROW_NUMBER to get only 1 row for each ID. Then MERGE:
;WITH cte AS (
SELECT g.*,
ROW_NUMBER() OVER (PARTTION BY SomeColumn ORDER BY SomeColumn) as rn
FROM Postion p
INNER JOIN geography g
ON p.someID = g.SomeID
)
MERGE ThirdTable as target
USING (SELECT * FROM cte WHERE rn = 1) as source
ON target.SomeID = source.SomeID
WHEN NOT MATCHED THEN
INSERT (target fields)
VALUES (source values);
// #temp contains all the required field from intersection of position and geography and will check it with third table using left join
select p.* into #temp
from position p inner join geography g
on p.id = g.id
insert into thirdtable
select * from #temp t1 left join thirdtable t2
on t1.id = t2.id
where t2.id is null
