Or onather way: -----------------------------------------------------
MERGE table1 AS target USING (SELECT 'val1' AS id, 'val2' AS name, 'val3' AS itemname, 'val4' AS itemcatName, 'val5' AS itemQty) AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name, itemname = source.itemname, itemcatName = source.itemcatName, itemQty = source.itemQty WHEN NOT MATCHED THEN INSERT (id, name, itemname, itemcatName, itemQty) VALUES (source.id, source.name, source.itemname, source.itemcatName, source.itemQty); ----------------------------------------------------- M. ---------- Původní e-mail ---------- Od: [email protected] Komu: [email protected] Datum: 8. 10. 2024 8:28:17 Předmět: Re: Inserting Unique Records into MSSQL Server from PostgreSQL Database "Hello, something like "UPSERT" ? maybe. -------------------------------------------------------- BEGIN TRANSACTION; UPDATE table1 SET name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5' WHERE id = 'val1'; IF @@ROWCOUNT = 0 BEGIN INSERT INTO table1(id, name, itemname, itemcatName, itemQty) VALUES('val1', 'val2', 'val3', 'val4', 'val5'); END COMMIT TRANSACTION; -------------------------------------------------------- M. ---------- Původní e-mail ---------- Od: Deepanshu Lodhi <[email protected]> Komu: [email protected] <[email protected]> Datum: 8. 10. 2024 8:12:25 Předmět: Inserting Unique Records into MSSQL Server from PostgreSQL Database " Hello everyone, I am currently working on a dataflow that selects data from a PostgreSQL database table and inserts records into an MSSQL Server database table. Unfortunately, due to an unexpected internet disconnection, some flow files have expired randomly. Now, I need to ensure that only unique records from the source table are inserted into the destination table, specifically those that do not already exist in the destination table. However, I have encountered a limitation with the “PutDatabaseRecord” processor, as it does not support the “INSERT IGNORE” property for MSSQL Server. I would appreciate any suggestions on the best approach to achieve this, so I can insert only the desired records without duplicating all records again. I’m using Apache NiFi version 2.0.0-M4. Thank you for your help! Best regards, Deepanshu " "
