Openrowset to pull data from excel in SQL Server:
Openrowset is a function which can be referenced after the "FROM" Clause of T-SQL.
This function is used to pull data from different databases by utlising the ODBC drivers installed in the particular windows system.
How to Pull the data from other SQL Server :
Syntax:
The below syntax is for the systems in which Excel 8.0 or lower versions are installed.
select * from openrowset('Microsoft.ACE.OLEDB.4.0','EXCEL 8.0;Database=D:\GOA.xls','select * from [sheet1$]')
The below syntax is for the systems in which Excel 8.0 or higher versions are installed.
select * from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=D:\GOA.xls','select * from [sheet1$]')
If we want to load the data from the same above excel into the SQL Server database, then below is the syntax:
use dbname
go
select * into dbo.newtablename from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=D:\GOA.xls','select * from [sheet1$]')
Go
Select * from dbo.newtablename
Go
Hope the above information helps
Thanks
