I remember that SharePoint list can lookup to SQL table directly (load table content to SP list), but what is this?
The only way is not to use a usual SharePoint List for looking up SQL data. You could use the app External List which can connect to a SQL Server view or table over Business Data Connectivity Service.
First of all enter Secure Store Service in Central Administration and add a credential with a user allowed to connect to the database. Remember the given name of the Secure Store Service object.
Then connect to SharePoint Central Administration with SharePoint Designer. Go to External Data and create a connection to the SQL table. You can choose the columns and filter values here. Please enter your Secure Store Object name when prompted.
After saving the changes to SharePoint you can find the connection in Business Data Connectivity Service in Central Administration.
Now switch to the site where you want to display the external data. Add an app for external list. Here you can select the Business Data Connectivity object. After a short loading time the external data will be displayed.
Having troubles? First attempt to troubleshoot should be the permissions.
The external list is a key building block solution on external data sources. This allows administrators to configure external sources to perform read, write, and delete operations from the SharePoint familiar list interface.
You can connect to 3 different data sources.
- .Net Type
- SQL Server
- WCF Service
Here are the key steps involved in the process.
- Prepare the external data source
- Configure SharePoint services and accounts
- Configure Secure Store Services
- Configure Business Data Connectivity Services
- Create an external content type
- Create an External List
- Create a view of external list
Here is a link to an MSDN article which explains all the steps above.
You can use Business Connectivity Services. Check below article: