sql导入导出脚本

--导入
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