Friday, January 9, 2015

MSSQL - Using Excel File as A LinkedServer

Add Excel file as a LinkedServer:

/****** Object:  LinkedServer [EXCELFILE]    Script Date: 9/9/2013 11:31:34 AM ******/
EXEC master.dbo.sp_dropserver @server=N'EXCELFILE', @droplogins='UserName'
GO

EXEC master.dbo.sp_addlinkedserver @server = N'EXCELFILE', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\TEMP\SomeExcelFile.xlsx', @provstr=N'Excel 12.0'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCELFILE', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
GO

INSERT INTO WebEx.dbo.WebEx_Host SELECT * FROM [EXCELFILE]...[Sheet1$]
GO

No comments:

Post a Comment