How do I populate a user-defined table type and put it in a stored procedure

Question

Our target database is working with stored procedures to load data. In order to do it fast, the developer on that side has given stored procedure with a user-defined table type (UDTT) as input.

In Alteryx I load some CSV files and then have a record set in memory. This record set is the same as my UDTT but I can't seem to figure out how to create the call to the stored procedure or even transform the data set to a UDTT.

BTW I'm a real n00b with Alteryx, my co-worker is on holiday

danilang · Answer

Hi @Casper

If it's a UDTT, then there isn't a way to call it directly from Alteryx.  In the past we've used stored procedures that accept an XML object that's passed as a string to pass large data sets to a stored proc.   Unfortunately, this doesn't help you until your DB person returns from holidays

Dan

Casper · Answer

@pedro no this is not what I meant. The calling of the storedproc is done because additional data has to be set in the database as well. As part of audit and tracing. So what I need is

* Data(recordset) in workflow
* Call sp with UDTT as parameter
* SP does the merge and sets other tables
* Done

pedrodrfaria · Answer

Hi @Casper

It seems that your issue is about outputting the dataset you have created as an Alteryx Workflow back to your database, is this correct?

To do this you can follow these steps:

1. Drag an Output Data Tool into the Canvas and connect to the Workflow.

2. Select the database you are trying to connect. I'm assuming you have created the connection already.

3. Write the name of the Table (use the same if you are trying to overwrite/append an existing table.

4. Select on the Output Options the method you want to go with about updating the table you have in your database. Then you would need to run the workflow.

Let us know if your question was not answered.

Pedro.