I have a request to archive SharePoint library (InfoPathform) items to a SQL database using Nintex workflow. Is there a way to use “Execute SQL” workflow action to write data from a library to SQL database? Currently on Nintex 2013 and SharePoint on Prem. Any help on how I can accomplish this will be helpful. Thanks for your help in advance!
Copying SharePoint list items to sql database
+4
+4If it is just the metadata, definitely you can use EXECUTE SQL action. All you need is a query like this:
INSERT INTO Table1
(Column1, column 2, column 3)
VALUES(
'{WorkflowVariable:val1}',
'{WorkflowVariable:val2}',
'{WorkflowVariable:val3}'
);
If you want the actual forms themselves copied to the SQL database as BLOBs, then I am not sure. I do not think you can achieve that with Execute SQL action.
Let us know.
+4Thanks Adavi that worked!
However I need to mover over 168 fields. Is there a way to do a bulk insert using an XML file?
Thanks for your help!
+4I personally have not done this, but looks like you could do it: Scripts to use XML to insert and update rows in a SQL Server table
+6I am not a developer and I could not find good instructions on how to use "Execute SQL", so bare with me.
Can I use references instead of variables?
INSERT INTO Table1
(Column1, column 2, column 3)
VALUES(
'{ItemProperty:Store}',
'{ItemProperty:ReasonCode}',
'{ItemProperty:AlarmReceived}'
);
+11What query can I use to update records on a SQL Table? I'm trying to update a SQL Table using the Execute SQL action where items have the same ID, without any luck. I used a previous INSERT INTO action sucessfully in a different workflow so my Connection string is good. I'm having issues with the Query.
My scenario is to have the SQL Table updated whenever the List item is modified based on the List Item ID which is a Primary key on the table.
UPDATE dbo.Table2
SET [Plant]=('{ItemProperty:Plant_x003a_}'),
[Employee]=('{ItemProperty:Employee_x003a_}'),
[Job Title]=('{ItemProperty:Job_x0020_Title_x003a_}'),
[Badge Number]=('{ItemProperty:Badge_x0020_number_x003a_}'),
[Course Name]=('{ItemProperty:CN}'),
[Date of Course]=('{ItemProperty:Date_x0020_of_x0020_course}'),
[Expiry date]=('{ItemProperty:Expiration_x0020_date}'),
[FacilityID]=('{ItemProperty:Facility_x003a_}'),
[CourseID]=('{ItemProperty:Course_x003a_}'),
[Employee Name]=('{ItemProperty:Employee_x0020_Name}'),
WHERE [ContentTypeID]=('{ItemProperty:ID}');
I do not get any errors, just items don't get updated in the table.
My scenario is to have the SQL Table updated whenever the List item is modified based on the List Item ID which is a Primary key on the table.
UPDATE dbo.Table2
SET [Plant]=('{ItemProperty:Plant_x003a_}'),
[Employee]=('{ItemProperty:Employee_x003a_}'),
[Job Title]=('{ItemProperty:Job_x0020_Title_x003a_}'),
[Badge Number]=('{ItemProperty:Badge_x0020_number_x003a_}'),
[Course Name]=('{ItemProperty:CN}'),
[Date of Course]=('{ItemProperty:Date_x0020_of_x0020_course}'),
[Expiry date]=('{ItemProperty:Expiration_x0020_date}'),
[FacilityID]=('{ItemProperty:Facility_x003a_}'),
[CourseID]=('{ItemProperty:Course_x003a_}'),
[Employee Name]=('{ItemProperty:Employee_x0020_Name}'),
WHERE [ContentTypeID]=('{ItemProperty:ID}');
I do not get any errors, just items don't get updated in the table.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.