Home / server / Questions / 28851
Accepted
Sean Earp
Asked: 2009-06-20 10:32:57 +0800 CST

How do I populate a SharePoint list with data from an external SQL database?

  • 772

This is a tossup between whether it belongs on Stack Overflow or Server Fault (but since I am not a developer, and am looking for a no-code solution, I'm starting with SF).

Is there an easy way to populate a SharePoint list with data from an external SQL database? In SharePoint designer, it is a simple matter to create a connection to a SQL database for using the data view web part, but I need the data from a SQL table to populate a SharePoint list. Is there a no-code way to do so?

Edit: Although there are certainly ways of exporting data from the SQL table and importing it into my list, I am looking to have the connection be live so that a column in my SharePoint list is always up-to-date with a master data source stored in a different SQL database.

sql-server sharepoint

4 Answers

  1. Best Answer

    Another option that you can look into is the Business Data Catalog. While this is more complicated it does allow you to have a SharePoint view of the data. The downside to this is that it does require the Enterprise edition and pretty advanced skills to get the BDC configured to point to your custom database.

    Since you said you are not a developer, I would tend to lead away from the BDC unless you are able to hire someone to configure this for you.

    • 5

  2. I would think you could:

    1. Create an Excel spreadsheet that has the SQL query to the database
    2. Execute the query and do a Copy Values copy in Excel and paste that to a new workbook
    3. Import that workbook when creating the Sharepoint list

    For displaying data live using a query, you need the Sharepoint Designer and you add a Data View to the list. See:

    http://office.microsoft.com/en-us/sharepointdesigner/HA100948041033.aspx

    • 1

  3. I took some time investigating this. In SP 2010 it is easy to import a table as an EXTERNAL SharePoint list. The data in the db and the list and is syncronized dynamically when someone modifies the table in the DB or the List in SP.

    But trying to create a workflow that writes data to and external SharePoint list...well you can do it but it involves a convoluted process that utilizes DB triggers and SQL CLR Assembly Stub.

    See: http://blogs.msdn.com/b/chaks/archive/2011/05/02/concept-leverage-sharepoint-workflows-with-external-lists-part-1.aspx for information on External Lists ans workflows.

    • 1

  4. If specifically wanting to populate info from SQL Server 2008. You can use SQL Server's Integration Services (SSIS)

    http://www.onlinetechblog.com/blog/index.php/2009/07/how-import-data-to-sharepoint-list-using-ssis/

    • 0