Commit to Alteryx DataBase through Alteryx

Hello Alteryx Champ,

I have connected SQL Developer to Alteryx and created a Table but i want to insert Data into the Alteryx which i was able to do using Text Input Tool.

After Running it was not writing to DataBase.

But in Alteryx we dont have commit option in Alteryx.

Can we have these Enhancement in Alteryx or is there any way we can do a Commit to Database through Alteryx

Developer

Developer Tools

Database Connection

Accepted answers

All comments

Treyson

Are you saying that you are unable to write back to a DB after the data comes in? Also you said you were bringing some data in through the text input tool, do you mean the "data input tool"

I think we need a little clarification before answering your question

ChrisTX

Can you post a sample workflow? And clarify what you mean by "After Running it was not writing to DataBase".

Chris

rohit782192

Well the Question i sSimple here.

In SQL We create Database and Insert into Database the values and do a Commit then only we are able to write into the Tables.

CREATE TABLE STUDENTS(

STUDENT_ID VARCHAR(7) NOT NULL,
STUDENT_FIRSTNAME VARCHAR(255),
STUDENT_MIDDLENAME VARCHAR(255),
STUDENT_LASTNAME VARCHAR(255),
STUDENT_CLASS VARCHAR(255),
STUDENT_AGE int,
STUDENT_SEX CHAR(1)
);
REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('V513674','ROHIT','TILAKRAJ','GUPTA','FIRST',22,'M');
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('R78192','VIKAS','TILAKRAJ','GUPTA','SECOND',23,'M');
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('N300820','SEEMA','TILAKRAJ','GUPTA','THIRD',24,'F');
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('R782193','MAYA','TILAKRAJ','GUPTA','FOURTH',25,'F');
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('S782192','AKSH','VIKAS','GUPTA','FIFTH',26,'M');
Insert into STUDENTS (STUDENT_ID,STUDENT_FIRSTNAME,STUDENT_MIDDLENAME,STUDENT_LASTNAME,STUDENT_CLASS,STUDENT_AGE,STUDENT_SEX) values ('R852741','GABBU','TILAKRAJ','GUPTA','PLAYGROUP',2,'M');

When you drag and Drop the Text Input tool in Alteryx and connect to sql Developer you will get the Tables Student you have created.

In the Text Input Tool Even you can write to the Table directly.

But when we run the workflow the data is not getting commited to SQL Developer.

ChrisTX

@rohit782192 can you provide a sample workflow?

You mentioned: When you drag and Drop the Text Input tool in Alteryx and connect to sql Developer you will get the Tables Student you have created.

But: are you talking about the Input Data tool, which is different from the Text Input tool?

You mentioned: In the Text Input Tool Even you can write to the Table directly.

But: neither the Text Input tool nor the Input Data tool writes to a table in a database. Something is missing here. Again, a sample workflow would help.

You mentioned: But when we run the workflow the data is not getting commited to SQL Developer.

I'm guessing your true need is: how do you update data in Alteryx and write the updated data back to a database table?

Chris

rohit782192

Hello Chris,

Yes i want to update data in DataBase through Alteryx

ChrisTX

I have not used Alteryx to update a database. I'm guessing there are some good tips if you search this community.

This post looks like the Output Data tool was used: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Updating-a-field-in-a-database/td-p/489599

When you click the Output Data tool in Alteryx, click the link for Open Example.

And here's the tool mastery article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Output-Data/ta-p/30023

Chris

rohit782192

Opps look like you dont have correct solution for me or alteryx need an Enhancement for the same.

rohit782192

Hey treyson yes.

Treyson

@rohit782192 You will need to have an output tool on your canvas. Alteryx can either create the table for you or you can output to an existing table. It sort of depends on how your table works. Alteryx has the ability to update/insert, append, truncate and append or drop the table and recreate.

@ChrisTX's links to the tool usage are the best place to start.

Quick Question I guess, do you have connections to your DB set up?

apathetichell

@rohit782192- there is a lot of complexity and nuance between different databases - what database type are your writing to. Are you reading from the same one? is this on canvas or in-db? Are you sure your connection to the db has been properly configured in ODBC64. Is it properly configured in Alteryx? There are lots of unknowns here - and when you fill in those details @Treyson or @ChrisTX or someone else (ie me) can give you more clarity on how you do what you are trying to do.

the short is - you can write from text input tool to a database using datastream in - it does not require a create table statement or manually setting the schema.

Nonsensely

I am having the same issue with Commitment control in Alteryx. I attempted to add a post-SQL command for "COMMIT," but it still abends when attempting to write to the table. I need to say, this is a DB2 database on an IBM iSeries. I have journaling and a trigger attached to this table. I am currently using IBM's Data Transfer tool to transfer from an excel sheet that brings in the data via Excel XML tools, from a web based XML source.

rohit782192

@Nonsensely I echo with you

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703