End goal is to do a bulk insert from a table to SQL
As per other topics I found that is a good idea to create a query that "prepares" the table to have the correct field names and then create a bulk insert query using GUI using the initial query as data source.
This sounds like a good idea but when trying even a simple query of the table I get an error as it doesn't understand the "table" parameter.
Never mind... I wasn't choosing the correct resource. Needed to make sure and choose the "Query JSON with SQL" resource.
I also need to update how I get the date to use a the correct syntax (still trying to figure it out)
Hi @Dataland! Glad to hear that you figured out your primary issue. If I understand correctly, you're still having some trouble formatting the date correctly - is that right?
@Darren that is correct, I was having issues with the date but the main issue ended being the GUI mode not accepting operations with triggers as per this topic response: Mssql insert failing - #2 by joeBumbaca
I needed to get a workaround by having the following steps:
Use JSON to SQL to gather the data from the table.
Get the "OK" button call the query to Insert the records on a temp "bridge" table I created (with a unique Id to make them unique for the user uploading the data).
On success call a stored procedure to get the records from the temp table associated to the unique id and process them mimicking what I had as the trigger. The stored procedure handled the date issue.
On success and failure call a query to truncate the records from the temp table for the id.
Not happy that I needed to go this route but seems to be working fine.
