[Ms-Sql]엑셀파일의 내용을 MsSQL Server에 가져오는 방법

반응형

--CONFIGURING SQL INSTANCE TO ACCEPT ADVANCED OPTIONS

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

 

GO

 

--ENABLING USE OF DISTRIBUTED QUERIES

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

 

--ADD DRIVERS IN SQL INSTANCE

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

--CONSULTING A SPREADSHEET

SELECT *

INTO TB_ECOD20

FROM  OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0; Database=F:\03.기술연구소\cjw\현대엔지니어링\EMS7-건축자원자료(16년01월분)\xls\ECOD20.xls; HDR=YES; IMEX=1',

'SELECT * FROM [Sheet1$]')

GO

 

--CONSULTING A SPREADSHEET

SELECT *

INTO TB_ECOD20

FROM  OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0; Database=F:\03.기술연구소\cjw\현대엔지니어링\EMS7-건축자원자료(16년01월분)\xls\2016\ECOD20.xlsx; HDR=YES; IMEX=1',

'SELECT * FROM [ECOD20$]')

GO

 

출처: https://use1348.tistory.com/65 [유용한 정보:티스토리]