Copying SharePoint list items to sql database

[rortiz312000]

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!

[prasannaadavi · Accepted solution]

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

[fhunth]

Instead sending them to sql , you could send them to another library.

[rortiz312000]

Thanks 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!

[prasannaadavi]

I 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

[espradli]

I 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}'

);

[prasannaadavi]

Emily,

Yes, that should work. You should be able to use the references or variables.

[claudiaurbanski]

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