BULK INSERT (T-SQL)
Copies a data file into a database table or view in a user-specified format.Syntax
BULK INSERT [['database_name'.]['owner'].]{'table_name' FROM data_file}
[WITH
(
[ BATCHSIZE [= batch_size]]
[[,] CHECK_CONSTRAINTS]
[[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']]
[[,] DATAFILETYPE [=
{'char' | 'native'| 'widechar' | 'widenative'}]]
[[,] FIELDTERMINATOR [= 'field_terminator']]
[[,] FIRSTROW [= first_row]]
[[,] FORMATFILE [= 'format_file_path']]
[[,] KEEPIDENTITY]
[[,] KEEPNULLS]
[[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]]
[[,] LASTROW [= last_row]]
[[,] MAXERRORS [= max_errors]]
[[,] ORDER ({column [ASC | DESC]} [,...n])]
[[,] ROWS_PER_BATCH [= rows_per_batch]]
[[,] ROWTERMINATOR [= 'row_terminator']]
[[,] TABLOCK]
)
] 前提是你需要文本文件改造一下,就是每5个字符后面加一个分隔符号.
然后就可以在查询分析器执行了
例子:BULK INSERT Northwind.dbo.[Order Details] FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n' )
Copies a data file into a database table or view in a user-specified format.Syntax
BULK INSERT [['database_name'.]['owner'].]{'table_name' FROM data_file}
[WITH
(
[ BATCHSIZE [= batch_size]]
[[,] CHECK_CONSTRAINTS]
[[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']]
[[,] DATAFILETYPE [=
{'char' | 'native'| 'widechar' | 'widenative'}]]
[[,] FIELDTERMINATOR [= 'field_terminator']]
[[,] FIRSTROW [= first_row]]
[[,] FORMATFILE [= 'format_file_path']]
[[,] KEEPIDENTITY]
[[,] KEEPNULLS]
[[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]]
[[,] LASTROW [= last_row]]
[[,] MAXERRORS [= max_errors]]
[[,] ORDER ({column [ASC | DESC]} [,...n])]
[[,] ROWS_PER_BATCH [= rows_per_batch]]
[[,] ROWTERMINATOR [= 'row_terminator']]
[[,] TABLOCK]
)
] 前提是你需要文本文件改造一下,就是每5个字符后面加一个分隔符号.
然后就可以在查询分析器执行了
例子:BULK INSERT Northwind.dbo.[Order Details] FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n' )
CREATE TABLE TEMP (
DATA VARCHAR(2000)
)
再把文本文件倒入这个表,即把整行倒入一个字段中,再
insert TableName (Col1,Col2,.......)
select
substring(data,1,5) as Col1, -------字符型写法
convert(numeric(10,2),substring(data,6,8)) as Col2, -------数字型写法
......
from temp呵呵,这样很好玩!
速度应该可以
大家想想办法
大家想想办法
使用者不懂电脑,我想把功能作进程序里,而不是利用SQLSERVER的导入功能
大家想想办法
使用者不懂电脑,我想把功能作进程序里,而不是利用SQLSERVER的导入功能
大家想想办法
先用
xp_cmdshell 'BCP ....' 倒入到TEMP表.
再insert TableName (Col1,Col2,.......)
select
substring(data,1,5) as Col1, -------字符型写法
convert(numeric(10,2),substring(data,6,8)) as Col2, -------数字型写法
......
from temp
只是个思路,呵呵!