存储过程导出数据到csv

USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- P_AutoInspect_LogToFilePath 'F:\1111\', 'database','sa','psw'
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P_AutoInspect_LogToFilePath]
                @DirPath            varchar(255),    --导出的文件路径
                @databaseName        varchar(255),    --数据库名字
                @User                varchar(100),    --用户名
                @Psw                varchar(100)    --密码

AS
BEGIN    
    
    exec sp_configure 'show advanced options',1    --开启配置
    reconfigure

    exec sp_configure 'xp_cmdshell',1    --开启配置
    reconfigure

    declare @FileName varchar(128);        --导出的文件名
    declare @TableName varchar(128);       --数据库表名

    declare @exec_sql varchar(1024);    --sql语句

    set @TableName = 'D_Log' + SUBSTRING(convert(varchar(20), getdate(), 112), 1,6)
    set @FileName ='Log' + CONVERT(varchar(100), GETDATE(), 112)+replace(CONVERT(varchar(100), GETDATE(), 108),':','') + '.csv'

    set  @exec_sql = 'bcp "select top 1 * from 
    + @databaseName + '.dbo.' + @TableName '
    + '" queryout "'+ @DirPath + @FileName + '" -c -t, -U "'+@User+'" -P "'+@Psw + '"'

    --print @exec_sql;

    EXEC master..xp_cmdshell @exec_sql;
END

GO

 

转载于:https://www.cnblogs.com/osbreak/p/9542528.html