将csv和txt 数据导入到数据库中

create staging table create table dbo.txt_staging ( col1 nvarchar(255), col2 nvarchar(255), col3 nvarchar(255) ) go --populate data bulk insert dbo.txt_staging from N‘D:\test.txt‘ WITH( FIELDTERMINATOR = ‘\t‘, ROWTERMINATOR = ‘\n‘ )

3，使用bulk insert 将csv的数据导入数据库中
CSV文件使用“，”作为列分隔符，使用“\n”作为行分隔符

--populate data 
bulk insert dbo.txt_staging
from N‘D:\abc.csv‘
WITH(
    FIELDTERMINATOR = N‘,‘,
    ROWTERMINATOR = N‘\n‘,
    CODEPAGE =N‘raw‘
)

 

Appendix

bulk insert的语法，经过简化如下

BULK INSERT  schema_name . table_name 
FROM ‘data_file‘ 
WITH 
(
FIELDTERMINATOR = ‘field_terminator‘,
ROWTERMINATOR = ‘row_terminator‘
);

FIELDTERMINATOR =‘field_terminator‘              

Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character).

ROWTERMINATOR =‘row_terminator‘

Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character).

 

参考doc：

BULK INSERT (Transact-SQL)

 

将csv和txt 数据导入到数据库中

标签：