No doubt you can craft your SQL or the database schema so that no duplicates get into MSSQL.
I'd probably use MQTT to pass the SQL statement from the Pi to the server. It's not trivial to guarantee delivery over an unreliable network but there are posts here describing an approach.
Use the same mechanism to pass back a list of inserted records and set a "transferred" flag in SQLite for reference in 5 minutes time.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy