bcp 导入/导出处理

最新推荐文章于 2021-03-18 17:58:09 发布

winjack11

最新推荐文章于 2021-03-18 17:58:09 发布

阅读量622

点赞数

分类专栏： SQLServer 文章标签： table cmd object user path null

本文链接：https://blog.csdn.net/winjack11/article/details/5528686

版权

SQLServer 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ExportFile]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ExportFile]
GO

CREATE PROCEDURE dbo.ExportFile
(

    @serverip    VARCHAR(10)
    , @user        VARCHAR(10)
    , @pass        VARCHAR(10)
    , @path        VARCHAR(100)
)
AS
BEGIN

    DECLARE @cmd varchar(1000)

    IF OBJECT_ID(N'tempdb..##IMPORT') IS NOT NULL
    BEGIN
        DROP TABLE ##IMPORT
    END

    IF OBJECT_ID(N'tempdb..##EXPORT') IS NOT NULL
    BEGIN
        DROP TABLE ##EXPORT
    END

    SELECT
        CASE WHEN amount < 0
               THEN
                   '-' +
                   REPLICATE ( '0' , 10 - LEN( CAST(amount * (-1) AS VARCHAR(11))) ) + CAST(amount * (-1) AS VARCHAR(11))
               ELSE
                   REPLICATE ( '0' , 11 - LEN( CAST(amount AS VARCHAR(11))) ) + CAST(amount AS VARCHAR(11))
        END AS amount

        , ISNULL(user, SPACE(6)) AS user
    INTO
        ##EXPORT
    FROM
        table1 WITH (NOLOCK)

    SELECT
        *
    INTO
        ##IMPORT
    FROM
        ##EXPORT
    WHERE
        1 = 0

    SET @cmd = 'bcp ##IMPORT in '+ @path + ' -S'+ @serverip + ' -U'+ @user + ' -P' + ISNULL(@pass, '') + ' -c -t/t -r/t/n'

    EXEC MASTER..XP_CMDSHELL @cmd

    SET @cmd = 'bcp "SELECT * FROM ##IMPORT UNION ALL SELECT * FROM ##EXPORT" queryout '+ @path + ' -S'+ @serverip + ' -U'+ @user + ' -P' + ISNULL(@pass, '') + ' -c -t/t -r/t/n'

    EXEC MASTER..XP_CMDSHELL @cmd


    DROP TABLE ##IMPORT
    DROP TABLE ##EXPORT

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO