--导入 load data local infile '/data/import/test3.csv' into table excel_order character set utf8mb4 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; load data local infile '/data/import/TB_FINANCE_PAYMENT.csv' into table TB_FINANCE_PAYMENT character set utf8mb4 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; --导入忽略ID自增 load data local infile '/tmp/sys_employee_rel_dept.csv' into table sys_employee_rel_dept character set GBK fields terminated by '$$' optionally enclosed by '"' lines terminated by '\n' (employee_id, dept_id, creator, creator_id, create_time); load data local infile '/tmp/crm_sms_group/crm_sms_group1.csv' into table crm_sms_group character set GBK fields terminated by '$$' optionally enclosed by '"' lines terminated by '\n' (id, group_name, dept_id, is_enabled, remark, creator, create_time, creator_id, editor, editor_id, edit_time, is_del, ver); load data local infile '/tmp/crm_sms_group/crm_sms_group2.csv' into table crm_sms_group character set GBK fields terminated by '$$' optionally enclosed by '"' lines terminated by '\n' (id, group_name, dept_id, is_enabled, remark, creator, create_time, creator_id, editor, editor_id, edit_time, is_del, ver); load data local infile '/tmp/crm_sms_group/crm_sms_group3.csv' into table crm_sms_group character set GBK fields terminated by '$$' optionally enclosed by '"' lines terminated by '\n' (id, group_name, dept_id, is_enabled, remark, creator, create_time, creator_id, editor, editor_id, edit_time, is_del, ver);
--导出Whir_Project_PackageSecLog DECLARE @total INT; DECLARE @size INT = 100000; DECLARE @page INT; DECLARE @index INT = 1; DECLARE @start INT; DECLARE @sql VARCHAR(2000); DECLARE @bcpcmd VARCHAR(2000); DECLARE @fileDirectory NVARCHAR(100) = 'D:\trade\Whir_Project_PackageSecLog\'; --导出文件存储目录 SELECT @total = COUNT(*) FROM Whir_Project_PackageSecLog; IF @total % @size = 0 BEGIN SET @page = @total / @size; END ELSE BEGIN SET @page = @total / @size + 1; END PRINT @page; WHILE ( @index <= @page ) BEGIN SET @start = ( @index - 1 ) * @size; SET @sql = 'select * from ppmoney_whtr_data_QY.dbo.Whir_Project_PackageSecLog order by LogId desc OFFSET ' + CONVERT(NVARCHAR(100), @start) + ' ROW FETCH NEXT ' + CONVERT(NVARCHAR(100), @size) + ' ROW ONLY'; SET @bcpcmd = 'bcp "' + @sql + '" queryout '+@fileDirectory+'Whir_Project_PackageSecLog' + CONVERT(NVARCHAR(100), @index) + '.csv -t , -c -T'; PRINT @bcpcmd EXEC master..xp_cmdshell @bcpcmd; SET @index = @index + 1; END