Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

In-Database Write Data Tool

hholland
7 - Meteor

When using the write data in-database tool, is there a way to update and append at the same time? I see separate options however not one that will do both. If they have to be done separate, how would I go about doing that?

I have a workflow that is creating a excel output. The in-database tools are looking to the excel spreadsheet and creating a temp table to use for the update. I would also like it to append any new items.

Thanks for your help!

Heather

4 REPLIES 4
Felipe_Ribeir0
16 - Nebula

Hi @hholland 

 

Unfortunately (i dont know why) the in-db write tool does not have an "updade, insert if new" option, so for this cases, to avoid complexity, i would suggest you to use the normal output tool with the "update, insert if new" option. 

 

Felipe_Ribeir0_0-1672849813543.png

 

hholland
7 - Meteor

@Felipe_Ribeir0. Thanks for the suggestion! I got some data to load into the SQL table.

 

I did receive the following error:

Error: Output Data (87): DataWrap2ODBC::SendBatch: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The statement has been terminated. Insert into "Consumers_Online_Services"("MemberNumber","FirstName","LastName","OLBMemberUserName","OLBRegistrationDate","OLBLastLoginDate","UsesMobileApp","MobileDeviceVendor","OLBUserStatus","BillPayEnrolled","SavvyMoneyEnrolled") Values (?,?,?,?,?,?,?,?,?,?,?)

 

The OLBLastLoginDate field is populating in the database table however the OLBRegistrationDate is not so I'm assuming that field is my data type issue. The only thing is that both fields are formatted as Date Time in Alteryx. They appear as 2022-12-26 21:30:42. They are both formatted as smalldatetime in the SQL table. I'm not sure I understand why the one date is loading that the other is not. Unless something else is going on.

 

Thanks!

Felipe_Ribeir0
16 - Nebula

Hi @hholland 

 

Yes, so the problem rely on some of these columns with the datatype smalldatetime, "OLBRegistrationDate" and/or "OLBLastLoginDate".

 

You probably have some cell inside these columns that is outside of the minimum/maximum range that the smalldatetime can handle (1900-01-01 to 2079-06-06).

 

I would see the min/max date of these columns with the summarize tool to see if this is true, and then change the datatype of these columns to datetime or datetime2 inside SQL Server.

 

https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transac...

 

Felipe_Ribeir0_0-1672855969557.png

 

hholland
7 - Meteor

I think that's it. I have a min date of 1899-12-30. I'm going to work on getting that cleaned up and see what happens. 

 

Thanks so much for all your help!

 

 

Labels