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:TestDataMagazineWebServiceMacauStore4XDatabaseData_2015-08-07phoneList.xls', 
10        'SELECT   * FROM [Sheet1$] ')
View Code