三通短信每月发送量导入Sqlserver随笔

创建表sql

CREATE TABLE SmsSentLog2014101625( 

Phone NVARCHAR(MAX), 

MessageContent NVARCHAR(MAX), 

MessageStatus NVARCHAR(MAX) ,

MessageCount  NVARCHAR(MAX) ,

MessageStatus1 NVARCHAR(MAX) ,

Province NVARCHAR(MAX) ,

GuiShuDi NVARCHAR(MAX) ,

SendTime NVARCHAR(MAX) ,

StatusTime NVARCHAR(MAX) 

) 

/**/从csv导入到表中，

//这是参考链接 http://www.cnblogs.com/xbf321/archive/2010/03/14/sql-server-import-csv-in-to-table.html

BULK INSERT SmsSentLog201410

FROM ‘F:\三通10月短信详单\dh1703 1026--1031.csv‘

WITH(

FIELDTERMINATOR = ‘","‘,

ROWTERMINATOR = ‘\n‘

)

SELECT COUNT(1) FROM SmsSentLog201410

 drop table SmsSentLog201410

update SmsSentLog201410 set MessageCount = REPLACE(MessageCount,‘"‘,‘‘)

delete from SmsSentLog201410 where  Phone =‘手机号码‘