• 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
Solved

How to query all add/delete tables in SDE and have a count of each as a row in the output

schulte.a
Rank
Badge +1

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!

icon

Best answer by nielsgerrits 19 April 2024, 15:54

View original

10 replies

nielsgerrits
Rank
Userlevel 6
Badge +33

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.

schulte.a
Rank
Badge +1

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

nielsgerrits
Rank
Userlevel 6
Badge +33

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.

schulte.a
Rank
Badge +1

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

nielsgerrits
Rank
Userlevel 6
Badge +33

Can you printscreen what you see?

schulte.a
Rank
Badge +1

 

nielsgerrits
Rank
Userlevel 6
Badge +33

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

nielsgerrits
Rank
Userlevel 6
Badge +33

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.

 

schulte.a
Rank
Badge +1

That worked! Thank you so much!

nielsgerrits
Rank
Userlevel 6
Badge +33

That worked! Thank you so much!

Cheers!

Reply


Community Stats

28,755
Posts
109,632
Replies
36,587
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.