-2

Here is my scenario:

I am using a Copy Data component having a Stored Procedure designated as the Source and a file as the Sink. The stored procedure returns several thousand records that get written to the file and I have a requirement to write the number of records written to another file.

What I'd like to do is to have the number of affected records from execution of the stored procedure stored in a variable b/c this same data process is repeated for several other files that get written and having the value stored in a variable will make things easy when time to write a summary file recording how many records were written to each file.

There are the several methods I know to work for acquiring the number of records but all of them require a second execution of the query or stored procedure in order to get the number of records. And since these queries can be extremely complex and time consuming; running them again to simply get a count of the affected records would be very inefficient and an unpractical approach.

Is there a way to capture the number of records returned after execution of the stored procedure using the Copy Data component within ADF?

Any expression of sorts or something???

UPDATED: For the sake of providing more clarity I am emphasizing that I am not asking how to acquire records from a stored procedure or query using T-SQL. I thought I was fairly clear in stating that when I said I "don't" want to make subsequent call to acquire the record count.

E.g. SELECT @@ROWCOUNT and similar methods.

Performing a @@ROWCOUNT after execution of the procedure won't allow me to cleanly write the resulting records to a file b/c it's adding yet another separate set of data to the output.

And SELECTing a COUNT() of records against the same query means executing the query again just for sake of acquiring a count.

The ask is to efficiently leverage the ADF to store the number of records affected from the execution to a variable W/O having the execute the same again.

Hopefully this adds any clarity that was previousy misunderstood.

Mark
  • 55
  • 1
  • 7
  • have you tried the manual https://learn.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver16 – nbk Aug 13 '23 at 17:05
  • Thanks for the response and my apology if I wasn't clear on describing the issue and the ask but no, your suggestion won't work b/c it would require a subsequent call to the procedure again. I don't want to initial plus subsequent calls requiring a repeated execution of the sproc b/c of previously mentioned concerns. Unless I'm missing something? – Mark Aug 13 '23 at 18:15
  • you make your uodate some where and the you run select @@ROWCOUNT and get the numbers, you don't need to call anything else no subquery or stored procedure again, if that doesn't help please elaborate with much more code and tables with data, what yoz wat maybe a https://dbfiddle.uk/ER_9PaV- with all that you have and want would also help – nbk Aug 13 '23 at 20:14
  • @nbk thanks for the suggestion but the query executed by the sproc are against multiple tables and also quite complex. I think the key point being missed is ADF portion of my question. However, seems SO also has pointed to the solutions that you've suggested and wants to close my question. So I did my own research and found what I was looking for here: https://stackoverflow.com/questions/51553141/azure-data-factory-check-rowcount-of-copied-records As you will see, the answer or solution I was looking for had nothing to do with TSQL. Thanks anyway – Mark Aug 13 '23 at 20:28

0 Answers0