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.
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. 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.