locked
What is the best way to get data into a SharePoint list from an SQL Db? RRS feed

  • Question

  • I have requirements to provide data from an SQL Db table into a native SP list, via a timer job. I am aware that with BCS, I can get the data into an external list but the external list does not provide the same functionality as a native list. Based on the research that I have done, I found the following steps from this post...

    1 - use BCS to create an external list
    2 - use PowerShell to export the external list into a csv file
    3- upload that csv file into a native SP list, giving me full control

    ...is there a better approach to this? What would be the advantage of first exporting the Db table into an external list, versus just using PowerShell to just export the table into csv directly (bypassing the BCS step)? Ultimately, I want to run the PowerShell script as a timer job so that I can pick up any updates.

    Do the steps above (1 though 3) sound like the best approach? Is there a better way to do this?

    Thanks for the ideas.

    Friday, August 26, 2016 8:26 PM

Answers

  • I ended up just exporting the data to a csv file and importing into a Sp native list, directly. I use this as a timer job, detecting if there is new data it should upload into the list based on the modification date. It seems to be running just fine, for now. 

    So I did not have to use BCS. 


    • Edited by Spawn10 Tuesday, September 6, 2016 3:57 PM
    • Marked as answer by Spawn10 Tuesday, September 6, 2016 3:57 PM
    Tuesday, September 6, 2016 3:57 PM

All replies

  • I would do the following, if its not external list.
    1. Create the custom list with the same schema as your DB table
    2. Create a PowerShell script to read the DB table contents and update the custom list (One time update)
    3. Write SQL trigger on add/update/delete and use CSOM/REST to update the custom list

    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    Friday, August 26, 2016 8:42 PM
  • I ended up just exporting the data to a csv file and importing into a Sp native list, directly. I use this as a timer job, detecting if there is new data it should upload into the list based on the modification date. It seems to be running just fine, for now. 

    So I did not have to use BCS. 


    • Edited by Spawn10 Tuesday, September 6, 2016 3:57 PM
    • Marked as answer by Spawn10 Tuesday, September 6, 2016 3:57 PM
    Tuesday, September 6, 2016 3:57 PM