Home >Tips > SharePoint Online from Scratch > Import data from SQL to SharePoint Online kalmstrom.com site map icon Site map  

Import data from SQL to SharePoint Online

A SharePoint Online tutorial by Peter Kalmström

SharePoint iconIn this demo Peter Kalmström, CEO and Systems Designer of kalmstrom.com Business Solutions, shows how to input data from an SQL Server database into SharePoint Online. It cannot be done directly. Instead Peter opens both the SharePoint list and the SQL Server database table in Access. Then he creates a query that copies the data from the SQL Server to SharePoint.

Create a new SharePoint list

  1. Go to the Settings gear and select Add an app.
  2. Click on the Contacts icon to create a new SharePoint list. A pop-up window will open where you can enter your Contact list name and click on Create.
  3. Open the new list. Add or remove list columns so that you get the list you prefer.

Open the SharePoint list in Access

  1. To open the list with Access, open the LIST tab and select Open with Access.
  2. In the window that opens, select the radio button for Link to data on the SharePoint Site and click OK. Now list will open in Access.

Connect the SQL Server to Access

  1. In Access, open the EXTERNAL DATA tab in the ribbon and click on the ODBC Database button.
  2. In the window that opens, check the radio button ?Link to the data source by creating a linked table? and click OK.
  3. In the Select Data Source dialog, click on New to create a new data source and use the SQL driver.
  4. Save the new data source.
  5. Change the default database to pick up the new data source.
  6. When you are back in the Select Data Source dialog again, select the new data source and link it to the Access table.
  7. Select BusinessEntityID as the Unique Record.
Now Peter has two connections in Access, with SQL and with SharePoint, and we can connect the SQL contacts database to the SharePoint contacts list. For that he creates two queries: one that selects data from the SQL database table and another one that appends it to the SharePoint list.

Create a query that selects data from an SQL database table and appends it to a SharePoint list

  1. Click on the Query Design button under the CREATE tab and select the SQL database table.
  2. Click on the Append button under the ribbon DESIGN tab to append data to the SharePoint list.
  3. Select how the data from the SQL database table should be added to the SharePoint list.
  4. Click on the View button under the ribbon DESIGN tab to view the result.
  5. If everything looks good, click on the Save icon.
  6. Run the query by clicking on the Run button under the ribbon DESIGN tab. Now the SQL Server data will be imported to the SharePoint list.

In next article we show how to update the imported SQL data..

Follow kalmstrom.com on LinkedInFacebook or Twitter!

back icon next icon

Products Buy FAQ Services Tips Books Contact About Us Tools

Security and integrity

Copyright  Kalmstrom Enterprises AB  All rights reserved