Delete records from db table

[rajamahajan85]

What is the easiest simpler way to run DELETE*FROM TEST1 query? Its quick to do odbc connection using input tool and put the SELECT*FROM TEST1 and get the result, however using same method when i write DELETE query, it errors out. I have both read and write access for the db table. I can use same DELETE query in the Sql server and b able to delete entire data table. Could someone provide suggestion?

[vizAlter]

@rajamahajan85 — Are you deleting everything from the Table while importing data through standard "Input" tool?

FYI, if you are using Input tool, the you can use standard "Filter" tool. You can put the condition in it for your "WHERE" clause (similar to SQL command).

If you are using "Data Stream In" tool, then there is a "Filter In-DB" tool.

(Hope, you are aware about both the tools, and did you try "Filter In-DB" tool?)

We use "Delete" to delete the records in SQL query, however, we use "Filter" to eliminate the records in Alteryx workflow.

[rajamahajan85]

Im not filtering data. I have a table in db which i need to first delete before i load data into it. My first step is to clean entire table and then append data to it. 

[ImadZidan]

Hello @rajamahajan85 ,

When specifying your output you can choose to empty records and then add the new ones.

Attached is a workflow. I have run it against a local sql server. It will five you an idea. The screen shot will show you the option. You have other options like pre Create SQL statement etc.

In summary:

1- Use the OutputData Tool to define the target table. Note: when defining the connection it will ask you for the table name you want to manipulate.

2- choose the option to delete and append data as shown in the screenshot.

One thing to mention, as it is the first time you are doing this , please backup your table before anything

I hope this is helpful.

[rajamahajan85]

.@ImadZidan - Thanks for sharing the solution .@ImadZidan

I followed the workflow which you put it below, however it runs fine. though it doesn't delete the table entire data instead it stay same.

-Input tool query only 16 records from entire table and then appending same to the table using output tool

- when i look at the end the table uu.auditf4, it has 100+ records like it had from beginning. it should only have 16 records which i queried from input tool.

-Thats the table. Below you can see it says 114 records.

[ImadZidan]

Hello @rajamahajan85 ,

mmm, I have just run the flow and it seems ok.

Ok, can you use another option. They both worked for me.

Otherwise, please attach workflow, may be I can spot something.

Another thing I would say, it could be that the insert is failing as a result of a constraint. Check warnings.

[rajamahajan85]

I tried other option of Overwrite table drop- that's not working, its giving an error. The first option works w/o any error and warning, its just not deleting the entire table instead have same no# of records like it had even though workflow message says it wrote 16 records.

Attaching the workflow.

thanks again for looking into.

Total records in the uu.aufitf4 table are 114

Input tool - Query is pulling 16 records

Output tool - Should append only 16 records before deleting the entire table.

[ImadZidan · Accepted solution]

Hello @rajamahajan85 ,

Ok lets try this as a test for now. Run the attached flow and check if it creates a new table called 

UU.auditf4tst. Also check if it has the 16 records

[ImadZidan]

@rajamahajan85 ,

Also, please if you get any error. I would like to see it. It helps

[rajamahajan85]

Awesome, it worked and now Delete and append also working. Seems like the table name which i was choosing to create/append was either not acceptable or quirked. 

i made a spell change in the table name and first create new tbl worked. I tried then delete and append for newer records and that worked too.

Thanks Imad for your help. Appreciate it.