三通簡訊每月發送量匯入Sqlserver隨筆

標籤：blog   http   ar   sp   on   2014   log   bs   html   

 

建立表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 =‘手機號碼‘

 

三通簡訊每月發送量匯入Sqlserver隨筆