Many ways to do it and here is one:
- Create a Linked Server in the SQL Server that corresponds to the excel file.
- The properties assigned to the Linked Server:
2. Enable adhoc distributed queries (be careful with this! It has security implications!)
3. SELECT * INTO [Table_Name] FROM EXCELDATA…[Sheet1$]
SQL Server 2008 supports 4 protocols using which a client can connect to the server. Their usage depends on the environment that the server is being used. The following protocols are available and a brief write up on what these are meant to do:
Shared Memory: This is the basic protocol that SQL Server 2008 supports and has no configurable settings. Considering that the clients using the Shared Memory protocol can connect only to a SQL Server instance running on the same computer, the usage of this protocol is pretty limited unless in troubleshooting. Shared Memory protocol cannot be used by anyone using MDAC 2.8 or earlier and if such a connection is attempted, Named Pipes protocol is used instead.
Named Pipes: A protocol developed for local area networks (LANs). A portion of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the ﬁrst) or remote (on a networked computer).
TCP/IP: Enabling SQL Server to use TCP/IP requires the most conﬁguration effort, but most networked computers are already properly conﬁgured.
Virtual Interface Adapter (VIA): A protocol that works with VIA hardware. This is a specialized protocol.