Well...
Quote:
after importing all new data I take all new transaction ids, and take all transaction ids for that client from database.
...
but I am struggling to find the most effective way. I wanted to do something like delete from transactions `where transactionId IN (transactionsDBString) and clientId = ClientID ` but that would delete both values and I only want new value to be deleted (and old value to stay)
I'd rather avoid of importing duplicate data. So, create a method/stored procedure which will insert only those data, which aren't duplicated. Something like:
INSERT INTO ExistingTable (...)
SELECT ...
FROM ImportedData IMDA
WHERE NOT EXISTS(
SELECT ...
FROM ExistingData EXDA
WHERE IMDA.ClientID = EXDA.ClientID AND IMDA.TransactionID = EXDA.TransactionID)
When you do that, you'll never be forced to delete duplicate data.
For further details, please see:
EXISTS (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Good luck!