How to convert a sql query result into a table

[ChrisPedrezuela]

Hi guys, im using pypyodbc to connect to sql and perform some joins between several tables. How can I directly convert the results into a stand-alone table within a geodatabase?

Regards,
Thanos

[PanagiotisPapadopoulos]

You can try create an in memory table

results = arcpy.CreateTable_management("in_memory", "VSResults", template, config_keyword)

then open a cursor for this table

cursor = arcpy.da.InsertCursor(results ,("ITEMID", "NAME", "YTPEN"))

loop into sql statement result end insert the sql data into the table

for result in cur:

     cursor.insertRow((result[0], result[1], result[2])

this table can be stored on geodatabase or set as output parameter

feature_set = arcpy.RecordSet()

feature_set.load(results)    

arcpy.SetParameter(4, feature_set)

[ChrisPedrezuela]

Thanks for that. Its should be just as straight forward as you wrote there, but the thing about my sql query is that I have like more than 20 columns from that results. If i will create a table first in the GDB, how can I read the columns and column type of my result and use it in creating the fields dynamically? In the part where we loop through the results of the query, if I have 20 columns, I don't want to write "cursor.insertRow((result[0], result[1], .......result[19])".

Thanks

[PanagiotisPapadopoulos]

Before create the table define a template for this table

template = "C:/tableschema/Projects/vstamplate.dbf"

config_keyword = ""

# Execute CreateTable

results = arcpy.CreateTable_management("in_memory", "VSResults", template, config_keyword)

this template can be pre configure with fields you are expecting from the query or try with python create this dbf file based on the SQL query results.

[PanagiotisPapadopoulos]

for the creation dbf files from python a have no experience but looking into the internet you can see some modules about that

Python modules for accessing .dbf (xBase) files

[JoshuaBixby]

The more specifics you provide, the more specific the feedback others can give.

What type of DMBS are you working with?  Is the database you are connecting to with pypyodbc a geodatabase?

Have you checked out the Make Query Layer and Make Table Query tools?

[ChrisPedrezuela]

Hi Joshua,

I'm working with SQL Server DB. I actually simplified the requirements of my query and managed to avoid using 20 tables and instead just three. So from there it was manageable to just add the results directly to my sde staging table. I think "Make Query Table" will also do as you've suggested but was quite a bit challenging to setup based on the number of table I was working with.