Reading, Manipulating, and Writing to SQL Server via In-DB Tools

Question

Hi all,

Looking for some help to understand if this is possible via In-DB tools in Alteryx. Our current process is wasting a significant amount of time using the Data Stream Out tool.

Can we do the following:

1. Pull various data tables from SQL Server #1

2. Perform various joins, filters, etc on data via In-DB tools

3. Write data to SQL Server #2 (without bringing data into the desktop)

Is the Data Stream Out necessary or can we go direct from one server to the other? If so, what does that process look like?

Thanks in advance,

DT

mbarone · Answer

Hmm....good question.  I myself have not used it to write in a completely different database on a different server.  I always assumed (perhaps incorrectly) that it would work if you structure it as you described.  If I look at the help files for that tool (https://help.alteryx.com/2019.4/LockInOutput.htm), it appears that it may only be able to write to the same database that was being connected to in the original connect in-db tool.

DataInDenver · Answer

Thanks @mbarone

Do you know how the "Write Data In-DB" tool would be configured if you were to write data out to a different server than the server you are querying from?

Typically in a SQL Server, data can be written to a linked server as long as the table is pre-fixed with the server_name + db_name + table_schema + table_name - an example is shown below:

INSERT INTO [Server#2].[DB_Name].[Table_Schema].[Table_name]

SELECT * FROM [Server#1].[DB_Name].[Table_Schema].[Table_name]

*This example illustrates inserting a table from Server#1 to Server#2 where the structure of the table from Server#1 already exists on Server#2*

So should the "Write Data In-DB" tool be configured in a similar fashion to the example above when writing the data out to a different a SQL Server?

mbarone · Answer

Yes, definitely possible, and is the beauty of the in db tools.  However, if you want to keep the whole workflow in db, and not pull it into memory (Alteryx), then you're limited to only using the tools shown on the In-Database palate (excluding of course the strem in/stream out).

So you might do something like: