Script Import and Export Table Sql Server (Sqlserver Excel)

1. สร้างเอกสาร Exel C:\Book1.xls 2. ทดสอบตามด้านล่าง

--Query Excel 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Book1.xls;HDR=YES', 
'SELECT * FROM [Sheet_Name$]')


 
--Export SQL table to Excel
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Book1.xls;', 
'SELECT * FROM [Sheet_Name$]') select * from Table_Name
 

--Import Excel to SQL table 
Insert into Table_Name 
Select * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\Book1.xls;HDR=YES', 
'SELECT * FROM [Sheet_Name$]')