Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • Data
  • How to query all add/delete tables in SDE and have a count of each as a row in the output

Hello, I am trying to create a workbench that will read in all our add/delete tables from our SDE, do a count on the running total of records for each, and export a spreadsheet with the table name, and the counts in each,

I’m using a SQL Creator and my SQL statement is something like SELECT COUNT(*) FROM <tablename>,and right now that's not returning me any value even though it was successful? Also, how would I go about coding out to add the next table in?

would it be:

SELECT COUNT(*) FROM <tablename>

union

SELECT COUNT(*) FROM <tablename2>

union

and so forth?

also for background:

We use Microsoft SQL Studio, and I chose the connection as a spatial server.

Thanks for any help!

You probably want to use an alias, like

SELECT COUNT(*) AS totalRecords FROM <tablename>

and then expose totalRecords.

Not sure about the union part, I think I would send one feature for each query, with the tableName as an attribute so you get something like

SELECT COUNT(*) AS totalRecords FROM @Value(tableName)

This way you will get a feature for each table with tableName and totalRecords, which can be directly written to excel.

Hmm, tried that and it’s a successful query, but the output is blank. 

I suspect the result is there but unexposed. If you select the record in the visual preview or in the Data Inspector, activate the Feature Information window and select the record. Then you will see everything about the feature on the right side of the screen.

Ah yes, I do see that its not exposed, but the property & data type columns are blank, but the value is correct. 

Can you printscreen what you see?

 

If you click on the arrow with unexposed attributes, you should be able to see the unexposed attribute names.

Can you show your SQL? The alias might need to be written in quotes.

SELECT COUNT(*) AS “totalRecords” FROM @Value(tableName)

 

I rarely use this nowadays so it is a bit dusty, sorry.

 

That worked! Thank you so much!

That worked! Thank you so much!

Cheers!

Reply


Community Stats

30,351
Posts
115,341
Replies
38,080
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.