Merging data into table using temp view

[pranav_k1]

I am trying to append data into table which already exists with some data in it.
I need to create view by joining multiple tables which later will be used to append data in final table .
I am able to alter table schema and then run query to insert data from view without error
Whenever I create temp view in one cell I am able to see data in that temp view in another cell but when I run merge query on final table it didn't reflect data in that final table
And also to mention that I have checked the merge query with other created dummy empty tables and then using same merge query on that dummy table which is working fine for me
What could be the possible reasons of this issue and What should I do to identify issue in this case?

[filipniziol]

Hi @pranav_k1 ,

I'm sorry to hear you're having trouble merging data into your table using a temporary view. I'd be happy to help you figure out what's going on.

Could you send the code you are trying to run?

• Code for Creating the Temporary View
• Merge Query
• Table Schemas

Without further details, I can suggest two general reasons that might be causing the issue:

• Scope of the Temporary View: Temporary views (CREATE TEMP VIEW) are session-scoped. If you're creating the view in one notebook and running the MERGE in another notebook, then the view may no longer exist. In that case you could try creating global temporary view, instead of temporary view (but it is not available in unity catalog)
• The merge conditions are not matching. Maybe simply in the test data you have a match, but not in the actual data in the temporary view when matched with the actual data in the target table. Check what is the output of the JOIN between your temporary view and target table using the same condition as in MERGE.

[pranav_k1]

Hi @filipniziol 
Thanks for your reply 
My issue is resolved as my fellow developer ran same commands with different name after sometime it worked successfully.
FYI - 
I was running query in same notebook just in different cells also I was running cells simultaneously so session based scope of temporary view should not be problem I believe as I previously mentioned I was able to see data in temp view
Also about merge conditions as I mentioned I tested that on dummy table so it was also working fine 
I used following merge query type

MERGE INTO <table name>
USING <query as view> 
   ON <primary key equality> 
WHEN MATCHED THEN UPDATE SET * 
WHEN NOT MATCHED THEN INSERT  

 
My observation - As I was working on gathering data had performed several operations in single notebook it get usually warning of larger notebook size and save session etc ,
I am suspecting that may be the cause of this error but still not 100% sure about it as I also ran tempview and merge query in new notebook in last time that also failed yesterday