Import a SQL Server table into a SharePoint list to use in InfoPath

Question

I need put the SQL table (with over 20000 records) into a SharePoint list, and use it in InfoPath.

Answer 1

The simplest of the ways that I can think right now is to export the sql server table data into an excel sheet and then import this excel data into a list (Site Actions >> View All Site Content >> Create >> Import Spreadsheet). Ah, by the way excel stops at 21730 maximum lines. So I told you this is a quick way to achieve this.

Thanks, Sachin

Answer 2

You can use BCS with an external content type to access your SQL table from a SharePoint list. There are limitations such as you can't export to Excel, and there may be limitations with InfoPath too.

You can use a PowerShell script or content import tool to import the data from SQL to SharePoint. E.g. export the table as a csv and iterate through each row to add a list.

There are multiple methods to getting external data into InfoPath, not just a SP List. See this MSDN link for more information: http://social.technet.microsoft.com/Forums/en/sharepoint2010customization/thread/1a6e38cc-53fe-45d8-9df4-c3ad325aa704