1、Bulk insert命令(SQL Server)用于将平面文件导入数据库表
- 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 xsxt.dbo.tabletest from 'c:/data.txt'
- with(
- FIELDTERMINATOR=',',
- ROWTERMINATOR='/n'
- )
示例2:
declare @sql varchar(1000)
set @sql = 'BULK INSERT dbo.KD_GZ_201301
FROM ''C:\宽带话单\KD_GZ_201301.log''
WITH (
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''|' + char(10) + ''')'
exec (@sql)
set @sql = 'BULK INSERT dbo.KD_GZ_201301
FROM ''C:\宽带话单\KD_GZ_201301.log''
WITH (
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''|' + char(10) + ''')'
exec (@sql)
2、两列数据,A\B列,有部分相同,找出A中除开B以外的数据(Excel,使用数据)
详见: http://jingyan.baidu.com/article/14bd256e163d24bb6d261204.html
定义A列为左边块、B列为右边的块
再在左边块的第一行输入
=IF(ISNA(VLOOKUP(A1,右边,1,FALSE)),"新",""),就可以查找到,关于这个 方法,需要对Vlookup稍微有点熟。
(后续更新)