IN-DB stream in vs Write-In-DB tool

[Joe_McCoy]

I have the need to push data into a server/db and am wondering that since the Data-Stream-In tool can create a table whether or not the Write-In-DB tool is necessary following the stream in tool as some sort of commit? I have had issues with trying to use the stream-in tool to actually create the table by itself.

Thanks,

Joe

[DiganP]

@Joe_McCoy In your scenario, the write in-db tool might not be necessary. What i have seen people do is create a temp table using the data stream in tool, manipulate the data set using the other tools then write out to the database using the write in-db tool. 

The reason why people use in-db tools is as below:

When processing data from a database, a standard Input Data tool downloads all of the specified input data to a temporary file on the local machine. All subsequent processing in Alteryx occurs locally. The download process has the potential to slow things down, especially when connecting to large tables in a database. The large input dataset (wherever it resides) has to be brought over the network to your computer and then processed. The in-db tools go around this.

You can learn more about in-db tools here. Did that help?

[Joe_McCoy]

@DiganP Thanks for the reply. I am basically moving a massive table (237MM rows) from one server to another using the in-db tools as we are not allowed to create a linked server so my workflow looks like the following and I wanted to know if the stream in tool could just write to the new server or is the write-in-db tool necessary. I have used the stream in tool in other work flows before and it never completed the actual writing of the table without the write tool at the end. Once the data is transferred, I have to transpose it into a shorter, wider table.

[DiganP · Accepted solution]

@Joe_McCoy  The data stream in tool should be able to create a table (if chosen). I don't think you need the write in-db tool within this design. You can transpose the table in the middle section (green to green), before using the data stream in tool. 

[Joe_McCoy]

Thanks @DiganP for your help. The table transfer takes so long that I do not want to add any complexity to it by transposing it. If I can avoid any errors on the load, I will worry about it later. I will try the stream in tool writing to a table on another smaller data set and see if I have been setting something up wrong.

Joe