Stored procedure, that Join two query, and write in another table

Question

I'm trying to write a stored procedure that inserts data into a table, but doesn't insert the data if there is already the same entry in the destination table.

The stored procedure should be able to also update a cell of an already existing row.

I'm using a table, and a view, and make an inner join of them.

So far I was trying to use a INSERT INTO, SELECT EXCEPT SELECT method.

When I run it, it write into the table but doesn't care if there is already an entry...

INSERT INTO DestinationTable
    SELECT data 
    FROM TABLE 
    INNER JOIN V_VIEW ON TABLE.ID = V_VIEW_ID
    EXCEPT 
    SELECT data 
    FROM DestinationTable 

Does anyone have an idea, why it doesn't work as intended ?

Also do you know a way to update the specific cell of the already existing row ?

The cell is a numeric data, it can be iterated.

Thank you guys, hope reading your answers soon!

Answer 1

I think SQL MERGE statements can be of great use in your scenario

MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
; 

Official Microsoft Documentation: SQL MERGE

For Examples and explanation: Using MERGE in SQL Server to insert, update and delete at the same time