I have data in a file geodatabase table that I want to write into an existing table in SQL Server (not enterprise-enabled) using Python. I am already using pyodbc to read SQL data elsewhere, but am open to using a different module to write this data. What is an example of the best practice to do this?

  • 1
    By "in SQL" do you mean "in Microsoft SQL Server"? How are you using pyodbc to read a file geodatabase? If there's no geometry in the table, and you don't want to use arcpy, this is probably more appropriate in Stack Overflow or Database Administrators ("How to insert rows using pyodbc?") – Vince Jul 17 '18 at 2:31
  • Specifically I do mean Microsoft SQL Server, but examples from other RDBMS could probably get me close as well. There is geometry but I am not writing it back. I plan to use Arcpy as far as it will take me. Particularly, should i just read from FGDB and insert to SQL row by row, or should I convert the entire table to an array or pandas data frame then insert all of that? – Rex Jul 17 '18 at 21:50
  • 1
    You can copy TableToTable in a dozen lines of code, if the database is geodatabase enabled. Formatting SQL statements could be accomplished in less that two score lines with an ArcSDESQLExecute cursor. There are so many alternatives that this is nearly too broad / opinion-based. – Vince Jul 17 '18 at 22:14
  • @Rex Please update the question with the specifics from your comment, and, when you post questions please include what you've actually tried yourself with verifyable source code. – om_henners Jul 17 '18 at 22:19
  • This question might in fact be too broad. Can it be deleted/closed out? – Rex Jul 17 '18 at 22:24

Give you have a connection to the SQL Server database already you should be able to make a connection pretty easily. You can then use the Copy Rows tool which will do the work for you, e.g.

input_data = '/path/to/data.gdb/featureclass'
output_data = '/path/to/connection.sde/tablename'

arcpy.CopyRows_management(input_data, output_data)

According to the docs any non-geometry column will be copied.

If you need to subset the original table columns, you can also create a table view first and use that instead of the original table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.