I have written one article to create linked server with Access database but I seen in many forums that people mostly would like to use excel file and wants to import its data. However, there are many ways to import Excel data to SQL Server but Linked server gives you more freedom over data. Let us see how we can do this. Before you start doing this, I would like you to create one excel 2007 file named “Dept” and put it in C: drive.
See my previous article for linked server with Access at
There will be two column in your excel 2007 file with following data in Sheet1
MIS IT department
Acct Account Department
Chem Chemist Department
Now let us move to SQL server and make linked server.
@server = 'DeptExcel',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Dept.xlsx',
@provstr = 'Excel 12.0'
--excel's credential if any
EXEC sp_addlinkedsrvlogin 'DeptExcel', 'false'
--list all tables from excel file
exec sp_tables_ex 'DeptExcel'
--see table in sheet1
SELECT * FROM [DeptExcel]...sheet1$
--create table in SQL and imports data
select * into tempTable FROM [DeptExcel]...sheet1$
--check your SQL table
select * from tempTable
Reference: Ritesh Shah