BULK INSERT
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 ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] 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 ]
)
]
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)This example specifies the FIRE_TRIGGERS argument.BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ':\n',
FIRE_TRIGGERS
)
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 ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] 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 ]
)
]
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)This example specifies the FIRE_TRIGGERS argument.BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ':\n',
FIRE_TRIGGERS
)
解决方案 »
- 不能对包含聚合或子查询的表达式执行聚合函数。高手,哪错了
- |zyciis| 查询关键字我添加了按拼音字母查询后如何在查询出来的数据高亮显示 谢谢 急 第四贴 一串字符的怎么办
- 从服务器中把数据插入到本地的语句
- 求一触发器~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 数据库连接问题
- -- 有關sql2000 小數位的問題, 還請高手指教!
- SQL Server连接服务器出错
- 在A表和B表中各新加一条记录时,与表B中更新表A的触发器功能冲突.
- 关于数据库还原的问题!
- 为SQL SERVER 2000配置ODBC时......
- 一个改表结构的问题!
- 表里把一字符串列相加如何写?
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料3.dbf]')
--*/--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
--*//**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:VFP98data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
select * from 表说明:
SourceDB=c: 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.
select col1=cast(col1 as varchar)
from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=d:\',
'select * from [0926.DBF]') as a
这样程序和转换内容分离了,以后万一结构有改动,只要修改一下DTS包就可以了.
而且DTS包功能很强大,万一转换失败也可以自动记录错误日志等等,方便查询.
用[openrowset/opendatasource/链接服务器的处理方式]来快速导入。
将导入的数据统统转换成为我需要的字段类型。很简单,只需要查询每个字段名的字段类型,然后改变。导出的时候也先导出,然后再改变字段类型。这样速度和字段都可以保证。
不过我没有这样做过,有没有哪位老大评估一下这个方法呢
into bbbb
from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=E:\vf data',
'select * from [A08_0401.dbf]') as a