Forum Discussion

sparvathaneni's avatar
sparvathaneni
New Contributor
8 years ago

Need counts of 20+ Tables from Source & Target into a .xls file

Hi - i am looking to capture counts of source and target tables into .csv file for data verification on a daily basics.
My Question is i was able to get a table count into a csv file after designing a snap

SQL Select ->Aggregate -->CSVFormater->Filewrite->Email (Looks ok ) Result - Good.

I have 20+ should i do the same process as above for each table ? or any simplified way of achieving the result ?

Any inputs is greatly appreciated .Thank you

4 Replies

  • robert_parks's avatar
    robert_parks
    New Contributor III
    8 years ago

    Is the purpose to get a count of all records in the table or is there individual criteria for each table?
    If you only need the count of all records, I would suggest creating a list of the tables you want to check and then have the pipeline read the list and execute a pipeline that does the steps you listed above. Your child pipeline would have a parameter to accept the table and that in the SQL Select.

    • sparvathaneni's avatar
      sparvathaneni
      New Contributor
      8 years ago

      Most tables have a date column but the issue is there are lot’s of NULLS in it .Not sure how to capture the counts .I am looking to compare counts in Source and Target based on the date column ,But things are getting tricky.

  • Aisha's avatar
    Aisha
    New Contributor
    3 years ago

    I have a similar, but instead of writing to a file writer, I need to write in S3 as a csv file.

    Can you share the snaplogic for retrieving the counts of records in multiple tables in one schema?

  • alchemiz's avatar
    alchemiz
    Contributor III
    3 years ago

    If the source is MSSQL then you can use SQL Execute snap and run below query

    select object_name(object_id) as [table name], sum(row_count) as [row count]
    from sys.dm_db_partition_stats
    where index_id < 2
    – and object_name(object_id) in (‘tbl1’,‘tbl2’,‘tbl3’)
    group by object_name(object_id)