rortiz312000
Nintex Newbie

Copying SharePoint list items to sql database

Jump to solution

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!

Reply
7 Replies
prasannaadavi
Nintex Newbie

Re: Copying SharePoint list items to sql database

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

View solution in original post

Reply
fhunth
Workflow Veteran

Re: Copying SharePoint list items to sql database

Jump to solution

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

Reply
rortiz312000
Nintex Newbie

Re: Copying SharePoint list items to sql database

Jump to solution

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!

0 Kudos
Reply
prasannaadavi
Nintex Newbie

Re: Copying SharePoint list items to sql database

Jump to solution

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

Reply
espradli
Process Pupil

Re: Copying SharePoint list items to sql database

Jump to solution

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

);

0 Kudos
Reply
prasannaadavi
Nintex Newbie

Re: Copying SharePoint list items to sql database

Jump to solution

Emily,

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

Reply
claudiaurbanski
Design Canvas Artiste

Re: Copying SharePoint list items to sql database

Jump to solution
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.
0 Kudos
Reply