SQL Server Merge a javascript list with a table

%3CLINGO-SUB%20id%3D%22lingo-sub-1573464%22%20slang%3D%22en-US%22%3ESQL%20Server%20Merge%20a%20javascript%20list%20with%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1573464%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20javascript%20object%20containing%20a%20bunch%20of%20user%20info%20that's%20sourced%20from%20Microsoft%20Teams%20team%20member%20list.%20I%20want%20to%20merge%20this%20object%20to%20a%20SQL%20table%20at%20predefined%20intervals.%20The%20team%20members%20of%20a%20team%20may%20be%20added%2Fremoved%20via%20Teams%20UI%20by%20users.%20However%2C%20my%20requirement%20is%20to%20keep%20the%20user%20table%20in%20SQL%20in%20sync%20will%20the%20actual%20team%20member%20list%20in%20Teams.%20I%20need%20a%20way%20to%20merge%20this%20list%20into%20the%20SQL%20table%20such%20that%20any%20existing%20users%20in%20the%20object%20kept%20intact%20in%20the%20db%2C%20any%20additional%20users%20are%20added%20and%20any%20unlisted%20users%20are%20removed%20from%20the%20db.%20I%20believe%20SQL%20merge%20operation%20could%20achieve%20this%20but%20that%20one%20works%20only%20if%20both%20the%20source%20and%20destination%20are%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20to%20achieve%20this%20when%20the%20source%20is%20a%20javascript%20object%20and%20destination%20is%20a%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586768%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%20Merge%20a%20javascript%20list%20with%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586768%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384773%22%20target%3D%22_blank%22%3E%40eynarain%3C%2FA%3E%26nbsp%3B--%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20easiest%20approach%20might%20be%20to%20break%20this%20task%20down%20into%20two%20distinct%20parts.%26nbsp%3B%20First%2C%20load%20the%20JSON%20data%20to%20a%20staging%20or%20temporary%20table%20in%20the%20database.%26nbsp%3B%20Next%2C%20perform%20your%20insert%2Fmerge%20into%20the%20primary%20base%20table.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20has%20good%20JSON%20support.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fjson%2Fjson-data-sql-server%3Fview%3Dsql-server-ver15%23key-json-capabilities-of-sql-server-and-sql-database%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fjson%2Fjson-data-sql-server%3Fview%3Dsql-server-ver15%23key-json-capabilities-of-sql-server-and-sql-database%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have a javascript object containing a bunch of user info that's sourced from Microsoft Teams team member list. I want to merge this object to a SQL table at predefined intervals. The team members of a team may be added/removed via Teams UI by users. However, my requirement is to keep the user table in SQL in sync will the actual team member list in Teams. I need a way to merge this list into the SQL table such that any existing users in the object kept intact in the db, any additional users are added and any unlisted users are removed from the db. I believe SQL merge operation could achieve this but that one works only if both the source and destination are tables.

 

Any idea how to achieve this when the source is a javascript object and destination is a table?

1 Reply

Hi @eynarain --

 

The easiest approach might be to break this task down into two distinct parts.  First, load the JSON data to a staging or temporary table in the database.  Next, perform your insert/merge into the primary base table.  

SQL Server has good JSON support.

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-...