U-SQL script to get multiple table count

Question

Need a U-sql script to get multiple table count.

eg the below query gives Employees count like wise i want to get more than one table count

@result =
SELECT COUNT(*) AS TotalRecordCount               
    FROM master.dbo.Employees;

OUTPUT @result
TO "/Output/ReferenceGuide/count/exampleA.csv"
USING Outputters.Csv();

Answer 1

You can use the UNION:

@table = 
    SELECT * FROM 
        ( VALUES
        (1, "Smith", 20),
        (1, "Smith", 20),
        (1, "Smith", 20),
        (2, "Brown", 30),
        (3, "Case", 40)
        ) AS T(id, name, age);

@countonetable =    
    SELECT "table1" AS TableName, 
           COUNT(*) AS CountRows,
           COUNT(DISTINCT name) AS CountNames            
    FROM @table;  

OUTPUT @countonetable
TO @"/Output/countonetable.txt"
USING Outputters.Csv(quoting : false);

@countmanytables =    
    SELECT "table1" AS TableName, COUNT(DISTINCT name) AS TableCount FROM @table
    UNION
    SELECT "table2" AS TableName, COUNT(*) AS TableCount FROM @table;  

OUTPUT @countmanytables
TO @"/Output/countmanytables.txt"
USING Outputters.Csv(quoting : false);