insert into temp table in databricks

Question

Hi Expert,

how we can use the as CT in databricks sql queries

as CT on       --------??

How we can use ifobject temp table in databricks sql queries

 IF OBJECT_ID(N'tempdb..##RETHINK_temp') IS NOT NULL      ------??  

how to use insert into temp table after select statement in databricks sql queries

Please refer below sql query

CREATE PROCEDURE [dbo].[THINKNEW] @CurrentVersion BIGINT, @LastVersion BIGINT                  

AS                  

BEGIN                  

  IF OBJECT_ID(N'tempdb..##RETHINK_temp') IS NOT NULL      ------??                

BEGIN                      

DROP TABLE #RETHINK_temp                

END      

select                        

RETHINK.NO,     

RETHINK.START_DATE,     

RETHINK.SourceSystemName    

INTO #RETHINK_temp       ----insert into temo table            

From RETHINK as RETHINK                

RIGHT OUTER JOIN CHANGETABLE (CHANGES RETHINK,                   

@LastVersion)                

as CT on       --------??

RETHINK.NO=CT.NO     

AND RETHINK.START_DATE=CT.START_DATE    

AND RETHINK.SourceSystemName=CT.SourceSystemName    

where CT.SYS_CHANGE_VERSION <=                   

@CurrentVersion                   

and RETHINK.Isdeleted='Y'                  

DELETE RETHINK                  

FROM RETHINK RETHINK                  

INNER JOIN #RETHINK_temp RETHINK_temp                  

  ON      

RETHINK.NO=RETHINK_temp.NO     

AND RETHINK.START_DATE=RETHINK_temp.START_DATE    

AND RETHINK.SourceSystemName=RETHINK_temp.SourceSystemName       

and RETHINK.Isdeleted='Y'                  

END

GO

query:

1)the changetable is not any table then what is mentioned below

RIGHT OUTER JOIN CHANGETABLE (CHANGES RETHINK,                   @LastVersion)   -----------unable to understand this   

what does this below query means and two dots are saying

 IF OBJECT_ID(N'tempdb..##RETHINK_temp') IS NOT NULL  

Answer 1

Hi Shambhu Rai ,

Thankyou for posting your query here.

I understand that your query is related to using SQL queries in Databricks to insert data into temporary tables.

We can't insert data into the temporary table but we can mimic the insert with union all (or) union(to remove duplicates).

Example:

#create temp view
spark.sql("""create or replace temporary view temp_view_t as select 1 as no, 'aaa' as str""")

spark.sql("select * from temp_view_t").show()
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#+---+---+

#union all with the new data
spark.sql("""create or replace temporary view temp_view_t as select * from temp_view_t union all select 2 as no, 'bbb' as str""")

spark.sql("select * from temp_view_t").show()                                                                     
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#|  2|bbb|
#+---+---+

#to eliminate duplicates we can use union also. 
spark.sql("""create or replace temporary view temp_view_t as select * from temp_view_t union select 1 as no, 'aaa' as str""")

spark.sql("select * from temp_view_t").show()
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#|  2|bbb|
#+---+---+

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou