Importing data into SqlServer from Excel

Excel spreadsheets can be imported into SQL server in various ways. The simple and quickest methods are with using Linked Servers and Distributed queries. The difference is how the connection is used. Linked servers uses a persistent configured connection where as Distributed queries uses a dynamic connection. Linked servers are not suitable when we want to import more than one document. For every document there a linked connection should be configured(painful).

You can configure Linked connection through Server Objects->Linked Servers-> New Linked Connection (See below image) from the Management studio object explorer. Use select statement with OPENQUERY to read excel data. Read more