sql 随笔 2015-08-07

xls 导入数据库

 1 --删除现有数据
 2 DELETE FROM  dbo.PhoneList 
 3 
 4 --插入数据
 5 insert into dbo.PhoneList 
 6 --读取xls数据
 7  select  RIGHT('0000000000'+CONVERT(VARCHAR,ROW_NUMBER() over(order by [Mobile Phone No_])),10)  ,isnull( [Mobile Phone No_],'') as [PhoneNo.], 0 as [EnableSend]
 8        from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 9        'Excel 8.0;HDR=YES;Database=E:\TestData\MagazineWebService\MacauStore4X\Database\Data_2015-08-07\phoneList.xls', 
10        'SELECT   * FROM [Sheet1$] ') 

View Code

 

转载于:https://www.cnblogs.com/xiaobuild/p/4709995.html