topic Re: To get Number of rows inserted after performing an Insert operation into a table in Data Engineering

To get Number of rows inserted after performing an Insert operation into a table

HariharaSam — Thu, 13 Jan 2022 07:45:58 GMT

Consider we have two tables A & B.

qry = """

INSERT INTO Table A

Select * from Table B where Id is null

"""

spark.sql(qry)

I need to get the number of records inserted after running this in databricks.

Re: To get Number of rows inserted after performing an Insert operation into a table

HariharaSam — Thu, 13 Jan 2022 12:39:34 GMT

Hi ,

I am getting an syntax error when I run @@ROW_COUNT after the insert statement.

I am running the code in Databricks

Re: To get Number of rows inserted after performing an Insert operation into a table

HariharaSam — Thu, 13 Jan 2022 13:06:19 GMT

Hi @Kaniz Fatma ,

I have tried the way you have mentioned but it still throws an error.

Re: To get Number of rows inserted after performing an Insert operation into a table

HariharaSam — Thu, 13 Jan 2022 13:46:57 GMT

Hi ,

My requirement here is I will be creating a function using Python code to perform insert operation to a Delta table , that is why I am running it in an Python cell.

I will be passing a table name to that function and I need to get the number of records inserted into the table once the function is executed.

So any solution to achieve this?

Re: To get Number of rows inserted after performing an Insert operation into a table

Hubert-Dudek — Fri, 14 Jan 2022 10:52:45 GMT

@@ROWCOUNT is rather T-SQL function not Spark SQL. I haven't found something like that in documentation but there is other way as every insert anyway return num_affected_rows and num_inserted_rows fields.

So you can for example use

df.first()['num_inserted_rows']

or subquery and select in sql syntax.

I am including example screenshots.

Re: To get Number of rows inserted after performing an Insert operation into a table

HariharaSam — Fri, 14 Jan 2022 14:21:31 GMT

Hi @Hubert Dudek

Your approach is working for me.

Thank you.

Re: To get Number of rows inserted after performing an Insert operation into a table

Hubert-Dudek — Fri, 14 Jan 2022 14:54:58 GMT

Great! Please when you can select as best answer.

Re: To get Number of rows inserted after performing an Insert operation into a table

TimThein — Wed, 14 Jun 2023 22:38:18 GMT

@Hubert Dudek, when I execute a similar piece of code in VSCode executed through databricks-connect, the dataframe contains 1 row with no columns, which is a problem. Executing the same code in a notebook on the same cluster works as you stated. Is this possibly a bug in databricks-connect?

Re: To get Number of rows inserted after performing an Insert operation into a table

GRCL — Thu, 15 Jun 2023 08:27:28 GMT

Almost same advice than Hubert, I use the history of the delta table :

df_history.select(F.col('operationMetrics')).collect()[0].operationMetrics['numOutputRows']

You can find also other 'operationMetrics' values, like 'numTargetRowsDeleted'.

Re: To get Number of rows inserted after performing an Insert operation into a table

Hari_Pakalpati — Thu, 22 May 2025 13:48:05 GMT

Hi Hubert, will this work with all DML operations and on all Spark versions? And can i use NUM_AFFECTED_ROWS for all DML operations?

Re: To get Number of rows inserted after performing an Insert operation into a table

User16653924625 — Wed, 23 Jul 2025 23:28:46 GMT

in case someone is looking for purely SQL based solution: (add LIMIT 1 to the query if you are looking for last op only)

select t.timestamp, t.operation, t.operationMetrics.numOutputRows as numOutputRows from ( DESCRIBE HISTORY <catalog>.<schema>.<table> ) t where t.operation like "%INSERT%" order by t.timestamp desc