SQL Server - C\u00f3mo exportar datos de la base a archivo de texto (CLR, OLE, BCP)

Hola, chicos,
¡Buenas noches!

Después de escribir mi publicación anterior donde hablé sobre Cómo importar archivos de texto a la base de datos (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET), Terminé emocionándome por escribir la parte 2, que es el proceso inverso, es decir, exportar los datos de SQL Server a un archivo de texto, que es exactamente esta publicación.

A menudo necesitamos exportar datos fácilmente a un archivo de texto y he visto a varios desarrolladores o administradores de bases de datos desesperados porque no saben cómo hacerlo de manera eficiente.

Arreglemos esto de una vez por todas.

Exportar a través de SQL Server Management Studio

Cómo exportar datos desde SQL Server Management Studio

Sin duda, la forma más sencilla y sencilla de exportar datos a un archivo en SQL Server es utilizando la interfaz propia de la herramienta, que es Management Studio.

En la pantalla del Explorador de objetos, haga clic derecho en la base de datos que contiene las tablas que desea exportar y seleccione la opción “Tareas” -> “Exportar datos…”

SQL Server - Export data to text file with Management Studio — SQL Server: exporte datos a un archivo de texto con Management Studio

En esta pantalla deberás elegir la fuente de datos, configurar los datos de conexión y la base de datos que exportarás.

SQL Server - Export data to text file with Management Studio 2 — SQL Server: exporte datos a un archivo de texto con Management Studio 2

En esta pantalla, debes elegir la fuente de destino de los datos. Para el archivo, elegí "Destino de archivo plano". Podemos definir dónde se escribirá el archivo con los datos, la codificación de caracteres (ISO-8859, UTF-8, etc.), el formato de los datos del archivo (delimitado por algún carácter, tamaño fijo o alineado a la derecha), el calificador de texto (p. ej. si defines las comillas como calificador, tu contraseña será algo como esto: “Dirceu”;”29″;”DBA”) y definir si la primera línea contendrá el encabezado de la tabla.

SQL Server - Export data to text file with Management Studio 3 — SQL Server: exporte datos a un archivo de texto con Management Studio 3

En esta pantalla, puede definir si desea seleccionar los objetos que desea exportar (primera opción) o si desea escribir una consulta y el resultado de la consulta se exportará al archivo. Para este ejemplo, elegiré la primera opción.

SQL Server - Export data to text file with Management Studio 4 — SQL Server: exporte datos a un archivo de texto con Management Studio 4

Aquí puede elegir el objeto que se exportará y definir el formato de salto de línea (Windows = CRLF, UNIX = LF) y el carácter delimitador de columna.

SQL Server - Export data to text file with Management Studio 5 — SQL Server: exporte datos a un archivo de texto con Management Studio 5

Finalmente, en esta pantalla defines si deseas exportar los datos (Ejecutar inmediatamente) y si deseas generar un paquete de Integration Services (SSIS) si deseas automatizar esta tarea como un Trabajo a través del Agente SQL o ejecutarlo manualmente sin tener que configurar todo nuevamente.

SQL Server - Export data to text file with Management Studio 6 — SQL Server: exporte datos a un archivo de texto con Management Studio 6

SQL Server - Export data to text file with Management Studio 7 — SQL Server: exporte datos a un archivo de texto con Management Studio 7

SQL Server - Export data to text file with Management Studio 8 — SQL Server: exporte datos a un archivo de texto con Management Studio 8

Aunque esto es sencillo, exportar varias tablas acaba siendo engorroso y poco práctico. Por esta razón, a continuación explicaré cómo hacer esto a través de T-SQL.

Exportación a través de BCP (copia masiva)

Cómo exportar datos de SQL Server a un archivo txt con BCP

Esta es una de las opciones más utilizadas por los DBA, ya que es sencilla, ya está instalada con SQL Server y se puede ejecutar tanto en paquetes SSIS como en procedimientos almacenados (usando xp_cmdshell).

Recuerde que para utilizar BCP, deberá activar la función xp_cmdshell. Personalmente, no me gusta usar xp_cmdshell o dejarlo habilitado en una instancia, ya que permite numerosas vulnerabilidades y cualquier comando de Windows Prompt se puede ejecutar con esta función habilitada.

Para activar la función y habilitar xp_cmdshell, ejecute los siguientes comandos:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

Ejemplo de uso:

-- Utilizando queryout, pode-se exportar o resultado de uma query
EXEC master.dbo.xp_cmdshell 'bcp "SELECT * FROM msdb.sys.tables" queryout "C:\Temp\bcp_queryout.csv" -c -t; -T -Slocalhost\SQL2014'

-- Utilizando out, pode-se exportar um objeto
EXEC master.dbo.xp_cmdshell 'bcp msdb.sys.tables out "C:\Temp\bcp_out.csv" -c -t, -T -Slocalhost\SQL2014'

SQL Server - Export data to CSV with BCP out queryout Results — SQL Server: exporte datos a CSV con resultados de consulta de salida BCP

Dónde:
– out y queryout le permiten definir cómo se exportan los datos. OUT exporta un objeto y QUERYOUT exporta el resultado de una consulta.
– -c define que todos los campos se exportarán como caracteres (cadena)
– -t; permite definir el separador de campos, no estando limitado a solo 1 carácter como separador. En el primer ejemplo, estoy usando el ";" como separador de columnas.
– -T sirve para informar que la conexión se realizará en modo Trusted Connection (Autenticación de Windows). Si desea utilizar la autenticación de SQL Server, simplemente use -Uusuario y -Psenha.
– -S se utiliza para informar el servidor\instancia al que desea conectarse.

Configuración de parámetros BCP:

Exportar con automatización OLE

Cómo exportar archivos de texto a la base de datos con OLE Automation

Para aquellos que no están familiarizados con esta característica, permite al DBA o desarrollador realizar una serie de acciones en la base de datos usando OLE DB, como leer/escribir/mover/copiar/eliminar archivos, crear hojas de cálculo en Excel y una serie de cosas más. La sintaxis es un poco similar a VBA y utiliza la API de Windows para estas operaciones.

Para ello utilizaremos los Procedimientos Almacenados stpEscribe_Arquivo_FSO y SaveDelimitedColumns, como se muestra a continuación:

Código fuente del Procedimiento stpEscribe_Arquivo_FSO

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (
    @String VARCHAR(MAX),
    @Ds_Arquivo VARCHAR(1501)
)
AS
BEGIN

    DECLARE
        @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Command VARCHAR(1000),
        @hr INT

    SET NOCOUNT ON

    SELECT
        @strErrorMessage = 'opening the File System Object'
    
    EXECUTE @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT

    
    IF @hr = 0
        SELECT
            @objErrorObject = @objFileSystem,
            @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objFileSystem,
            'CreateTextFile',
            @objTextStream OUT,
            @Ds_Arquivo,
            2,
            True

    IF @hr = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Write',
            NULL,
            @String

    
    IF @hr = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
    IF @hr = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Close'

    
    IF @hr <> 0
    BEGIN
    
        DECLARE
            @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT
    
        EXECUTE sp_OAGetErrorInfo
            @objErrorObject,
            @Source OUTPUT,
            @Description OUTPUT,
            @Helpfile OUTPUT,
            @HelpID OUTPUT
        
        
        SELECT
            @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
        RAISERROR (@strErrorMessage,16,1)
        
    END
    
    
    EXECUTE sp_OADestroy
        @objTextStream
    
    EXECUTE sp_OADestroy
        @objTextStream
        
END

Código fuente del procedimiento SaveDelimitedColumns
Para ver el código fuente de este procedimiento, creado por John Buoro, acceda este enlace o por debajo:

CREATE PROCEDURE [dbo].[SaveDelimitedColumns]
    @PCWrite VARCHAR(1000) = NULL,
    @DBFetch VARCHAR(4000),
    @DBWhere VARCHAR(2000) = NULL,
    @DBThere VARCHAR(2000) = NULL,
    @DBUltra BIT = 1,
    @Delimiter VARCHAR(100) = 'CHAR(44)', -- Default is ,
    @TextQuote VARCHAR(100) = 'CHAR(34)', -- Default is "  Use SPACE(0) for none.
    @Header BIT = 0, -- Output header. Default is 0.
    @NullQuoted BIT = 0,
    @DateTimeStyle TINYINT = 120 -- CONVERT Date Time Style. Default is ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @Return INT;
    DECLARE @Retain INT;
    DECLARE @Status INT;

    SET @Status = 0;

    DECLARE @TPre VARCHAR(10);

    DECLARE @TDo3 TINYINT;
    DECLARE @TDo4 TINYINT;

    SET @TPre = '';

    SET @TDo3 = LEN(@TPre);
    SET @TDo4 = LEN(@TPre) + 1;

    DECLARE @DBAE VARCHAR(40);
    DECLARE @Task VARCHAR(6000);
    DECLARE @Bank VARCHAR(4000);
    DECLARE @Cash VARCHAR(2000);
    DECLARE @Risk VARCHAR(2000);
    DECLARE @Next VARCHAR(8000);
    DECLARE @Save VARCHAR(8000);
    DECLARE @Work VARCHAR(8000);
    DECLARE @Wish VARCHAR(MAX);

    DECLARE @Name VARCHAR(100);
    DECLARE @Same VARCHAR(100);

    DECLARE @Rank SMALLINT;
    DECLARE @Kind VARCHAR(20);
    DECLARE @Mask BIT;
    DECLARE @Bond BIT;
    DECLARE @Size INT;
    DECLARE @Wide SMALLINT;
    DECLARE @More SMALLINT;

    DECLARE @DBAI VARCHAR(2000);
    DECLARE @DBAO VARCHAR(8000);
    DECLARE @DBAU VARCHAR(MAX);

    DECLARE @Fuse INT;
    DECLARE @File INT;

    DECLARE @HeaderString VARCHAR(8000);
    DECLARE @HeaderDone INT;

    SET @DBAE = '##SaveFile' + RIGHT(CONVERT(VARCHAR(10), @@SPID + 100000), 5);

    SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE;

    EXECUTE ( @Task );

    SET @Bank = @TPre + @DBFetch;

    IF NOT EXISTS
    (
        SELECT
            *
        FROM
            sysobjects
        WHERE
            RTRIM(type) = 'U'
            AND name = @Bank
    )
    BEGIN
        SET @Bank = CASE WHEN LEFT(LTRIM(@DBFetch), 6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END;
        SET @Bank = REPLACE(@Bank, CHAR(94), CHAR(39));
        SET @Bank = REPLACE(@Bank, CHAR(45) + CHAR(45), CHAR(32));
        SET @Bank = REPLACE(@Bank, CHAR(47) + CHAR(42), CHAR(32));
    END;

    IF @DBWhere IS NOT NULL
    BEGIN
        SET @Cash = REPLACE(@DBWhere, 'WHERE', CHAR(32));
        SET @Cash = REPLACE(@Cash, CHAR(94), CHAR(39));
        SET @Cash = REPLACE(@Cash, CHAR(45) + CHAR(45), CHAR(32));
        SET @Cash = REPLACE(@Cash, CHAR(47) + CHAR(42), CHAR(32));
    END;

    IF @DBThere IS NOT NULL
    BEGIN
        SET @Risk = REPLACE(@DBThere, 'ORDER BY', CHAR(32));
        SET @Risk = REPLACE(@Risk, CHAR(94), CHAR(39));
        SET @Risk = REPLACE(@Risk, CHAR(45) + CHAR(45), CHAR(32));
        SET @Risk = REPLACE(@Risk, CHAR(47) + CHAR(42), CHAR(32));
    END;

    SET @DBAI = '';
    SET @DBAO = '';
    SET @DBAU = '';

    SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM (' + @Bank + ') AS T WHERE 0 = 1';
    
    IF @Status = 0
        EXECUTE ( @Task );
    SET @Return = @@ERROR;
    IF @Status = 0
        SET @Status = @Return;

    -- For all columns (Fields) in the table.
    DECLARE Fields CURSOR FAST_FORWARD FOR
    SELECT
        '[' + C.name + ']',
        C.colid,
        T.name,
        C.isnullable,
        C.iscomputed,
        C.length,
        C.prec,
        C.scale
    FROM
        tempdb.dbo.sysobjects AS O
        JOIN tempdb.dbo.syscolumns AS C ON O.id = C.id
        JOIN tempdb.dbo.systypes AS T ON C.xusertype = T.xusertype
    WHERE
        O.name = @DBAE
    ORDER BY
        C.colid;

    SET @Retain = @@ERROR;
    IF @Status = 0
        SET @Status = @Retain;

    OPEN Fields;

    SET @Retain = @@ERROR;
    IF @Status = 0
        SET @Status = @Retain;

    FETCH NEXT FROM Fields
    INTO
        @Same,
        @Rank,
        @Kind,
        @Mask,
        @Bond,
        @Size,
        @Wide,
        @More;

    SET @Retain = @@ERROR;
    IF @Status = 0
        SET @Status = @Retain;

    -- Convert to character for header.
    SET @HeaderString = '';
    DECLARE @sql NVARCHAR(4000);
    DECLARE @cDelimiter NVARCHAR(9);
    DECLARE @cTextQuote NVARCHAR(9);
    DECLARE @TypeFound BIT;
    SET @sql = N'select @cDelimiter = ' + @Delimiter;
    EXEC sp_executesql @sql, N'@cDelimiter varchar(9) output', @cDelimiter OUTPUT;
    SET @sql = N'select @cTextQuote = ' + @TextQuote;
    EXEC sp_executesql @sql, N'@cTextQuote varchar(9) output', @cTextQuote OUTPUT;

    WHILE @@FETCH_STATUS = 0
          AND @Status = 0
    BEGIN
        SET @TypeFound = 0;

        -- Build header.
        IF LEN(@HeaderString) > 0
            SET @HeaderString = @HeaderString + @cDelimiter + ISNULL(@cTextQuote + REPLACE(@Same, @cTextQuote, REPLICATE(@cTextQuote, 2)) + @cTextQuote, SPACE(0));
        IF LEN(@HeaderString) = 0
            SET @HeaderString = ISNULL(@cTextQuote + REPLACE(@Same, @cTextQuote, REPLICATE(@cTextQuote, 2)) + @cTextQuote, SPACE(0));

        IF @Kind IN ( 'char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname', 'xml' )
        BEGIN
            IF @NullQuoted = 0
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',SPACE(0))';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',SPACE(0))';
            END;
            IF @NullQuoted = 1
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+REPLACE(' + @Same + ' COLLATE SQL_Latin1_General_CP1_CI_AI,' + @TextQuote + ',REPLICATE(' + @TextQuote + ',2))+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
            END;
            SET @TypeFound = 1;
        END;

        IF @Kind IN ( 'bit', 'tinyint', 'smallint', 'int', 'bigint' )
        BEGIN
            IF @NullQuoted = 0
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))';
            END;
            IF @NullQuoted = 1
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')';
            END;
            SET @TypeFound = 1;
        END;

        IF @Kind IN ( 'numeric', 'decimal', 'money', 'smallmoney', 'float', 'real' )
        BEGIN
            IF @NullQuoted = 0
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),SPACE(0))';
            END;
            IF @NullQuoted = 1
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(CONVERT(varchar(128),' + @Same + '),' + @TextQuote + '+' + @TextQuote + ')';
            END;
            SET @TypeFound = 1;
        END;

        IF @Kind IN ( 'uniqueidentifier', 'geometry', 'geography' )
        BEGIN
            IF @NullQuoted = 0
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',SPACE(0))';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',SPACE(0))';
            END;
            IF @NullQuoted = 1
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
            END;
            SET @TypeFound = 1;
        END;

        IF @Kind IN ( 'datetime2', 'datetime', 'smalldatetime', 'time', 'date', 'datetimeoffset' )
        BEGIN
            IF @NullQuoted = 0
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',SPACE(0))';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',SPACE(0))';
            END;
            IF @NullQuoted = 1
            BEGIN
                IF @Rank = 1
                    SET @DBAU = ' ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
                IF @Rank > 1
                    SET @DBAU = @DBAU + '+' + @Delimiter + '+ISNULL(' + @TextQuote + '+CONVERT(varchar(128),' + @Same + ',' + CONVERT(VARCHAR(3), @DateTimeStyle) + ')+' + @TextQuote + ',' + @TextQuote + '+' + @TextQuote + ')';
            END;
            SET @TypeFound = 1;
        END;

        IF @TypeFound = 0
        BEGIN
            SET @Retain = 'ERROR: Data type ' + UPPER(@Kind) + ' was used but not supported by SaveDelimitedColumns.';
            SET @Status = @Retain;
        END;

        FETCH NEXT FROM Fields
        INTO
            @Same,
            @Rank,
            @Kind,
            @Mask,
            @Bond,
            @Size,
            @Wide,
            @More;

        SET @Retain = @@ERROR;
        IF @Status = 0
            SET @Status = @Retain;
    END;

    CLOSE Fields;
    DEALLOCATE Fields;

    IF LEN(@DBAU) = 0
        SET @DBAU = '*';

    IF @PCWrite IS NOT NULL
       AND ( @DBUltra = 0 )
       AND ( @Header = 1 )
    BEGIN
        SET @HeaderString = REPLACE(@HeaderString, '"', '""');
        SET @DBAI = ' SELECT ' + CHAR(39) + @HeaderString + CHAR(39) + ' UNION ALL SELECT ';
    END;
    ELSE
        SET @DBAI = ' SELECT ';

    SET @DBAO = ' FROM (' + @Bank + ') AS T' + CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END + CASE WHEN @DBThere IS NULL THEN '' ELSE ' ORDER BY ' + @Risk END;

    -- Output where @DBUltra = 0 (Uses XP_CMDSHELL \ BCP)
    IF @PCWrite IS NOT NULL
       AND @DBUltra = 0
    BEGIN
        SET @Wish = 'USE ' + DB_NAME() + @DBAI + @DBAU + @DBAO;
        SET @Work = 'BCP "' + @Wish + '" QUERYOUT "' + @PCWrite + '" -w -T -S "' + @@SERVERNAME + '" ';
        -- PRINT @Work
        EXECUTE @Return = master.dbo.xp_cmdshell @Work, NO_OUTPUT;
        SET @Retain = @@ERROR;
        IF @Status = 0
            SET @Status = @Retain;
        IF @Status = 0
            SET @Status = @Return;

        IF @Status <> 0
            GOTO ABORT;
    END;

    -- Output where @DBUltra = 1 (Uses Ole Automation)
    IF @PCWrite IS NOT NULL
       AND @DBUltra = 1
    BEGIN
        IF @Status = 0
            EXECUTE @Return = sp_OACreate 'Scripting.FileSystemObject', @Fuse OUTPUT;
        SET @Retain = @@ERROR;
        IF @Status = 0
            SET @Status = @Retain;
        IF @Status = 0
            SET @Status = @Return;

        IF @Status = 0
            EXECUTE @Return = sp_OAMethod @Fuse, 'CreateTextFile', @File OUTPUT, @PCWrite, -1;
        SET @Retain = @@ERROR;
        IF @Status = 0
            SET @Status = @Retain;
        IF @Status = 0
            SET @Status = @Return;

        IF @Status <> 0
            GOTO ABORT;
    END;

    SET @DBAI = 'DECLARE Records CURSOR GLOBAL FAST_FORWARD FOR' + @DBAI;

    
    IF @Status = 0
        EXECUTE ( @DBAI + @DBAU + @DBAO );
    SET @Return = @@ERROR;
    IF @Status = 0
        SET @Status = @Return;

    OPEN Records;
    SET @Retain = @@ERROR;
    IF @Status = 0
        SET @Status = @Retain;

    FETCH NEXT FROM Records
    INTO
        @Next;
    SET @Retain = @@ERROR;
    IF @Status = 0
        SET @Status = @Retain;

    -- Header.
    SET @HeaderDone = 0;
    WHILE @@FETCH_STATUS = 0
          AND @Status = 0
    BEGIN
        IF @PCWrite IS NOT NULL
           AND @DBUltra = 1
        BEGIN
            -- Write header (FILE).
            IF ( @Header = 1 )
               AND ( @HeaderDone = 0 )
            BEGIN
                SET @Save = @HeaderString + CHAR(13) + CHAR(10);
                IF @Status = 0
                    EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save;
                SET @HeaderDone = 1;
            END;

            -- Write the data (FILE).
            SET @Save = @Next + CHAR(13) + CHAR(10);
            IF @Status = 0
                EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save;
            IF @Status = 0
                SET @Status = @Return;
        END;

        IF @PCWrite IS NULL
        BEGIN
            -- Print header (TEXT).
            IF ( @Header = 1 )
               AND ( @HeaderDone = 0 )
            BEGIN
                PRINT @HeaderString + CHAR(13) + CHAR(10);
                SET @HeaderDone = 1;
            END;
            PRINT @Next;
        END;

        FETCH NEXT FROM Records
        INTO
            @Next;
        SET @Retain = @@ERROR;
        IF @Status = 0
            SET @Status = @Retain;
    END;

    CLOSE Records;
    DEALLOCATE Records;

    -- Close output file (Ole Automation)
    IF @PCWrite IS NOT NULL
       AND @DBUltra = 1
    BEGIN
        EXECUTE @Return = sp_OAMethod @File, 'Close', NULL;
        IF @Status = 0
            SET @Status = @Return;

        EXECUTE @Return = sp_OADestroy @File;
        IF @Status = 0
            SET @Status = @Return;

        EXECUTE @Return = sp_OADestroy @Fuse;
        IF @Status = 0
            SET @Status = @Return;
    END;

    ABORT: -- This label is referenced when OLE automation fails.

    IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000
        RAISERROR('SaveDelimitedColumns Windows Error [%d]', 16, 1, @Status);

    SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE;
    EXECUTE ( @Task );

    RETURN ( @Status );

END;
GO

Ejemplo de uso:

-- Exportando uma string para arquivo com OLE Automation
DECLARE @Texto VARCHAR(MAX) = 'Teste
de arquivo

com quebra
de
linhas
'

EXEC dbo.stpEscreve_Arquivo_FSO 
    @String = @Texto, -- varchar(max)
    @Ds_Arquivo = 'C:\Temp\Teste.txt' -- varchar(1501)


-- Exportando para CSV com OLE Automation
EXEC dbo.SaveDelimitedColumns
    @DBFetch='select * from Testes.dbo.Teste',
    @DBWhere='Tipo = ^Teste 2^',
    @PCWrite='C:\Temp\Teste.csv',
    @Header = 1

SQL Server - Export data to text file with OLE Automation 2 — SQL Server: exporte datos a un archivo de texto con OLE Automation 2

Exportar con Common Language Runtime (CLR)

Cómo exportar archivos de texto al banco con CLR

El CLR permite crear rutinas (procedimientos almacenados, funciones, disparadores, etc.) escritas en C#, F# y VB.NET, compilarlas y ejecutarlas en la base de datos de forma nativa, ampliando las capacidades del DBMS, ya que es posible crear multitud de cosas que no serían posibles usando solo Transact-SQL, como manipulación de archivos, carga y descarga de archivos vía FTP, funciones de agregación, integración con Webservices y mucho más.

Para ello utilizaremos los Procedimientos Almacenados stpExporta_Query_Txt y stpEscribe_Arquivo, como se muestra a continuación:

Código fuente del procedimiento stpExporta_Query_Txt

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpExporta_Query_Txt(string query, string separador, string caminho, int Fl_Coluna)
    {

        var fileStream = new FileStream(caminho, FileMode.Create);
        var sw = new StreamWriter(fileStream, Encoding.Default);

        try
        {
            using (var conn = new SqlConnection("context connection=true"))
            {

                var getOutput = new SqlCommand
                {
                    CommandText = query,
                    CommandType = CommandType.Text,
                    CommandTimeout = 120,
                    Connection = conn
                };

                
                conn.Open();

                var exportData = getOutput.ExecuteReader();

                if (Fl_Coluna == 1)
                {
                    for (var i = 0; i < exportData.FieldCount; i++)
                    {
                        sw.Write(exportData.GetName(i));
                        if (i < exportData.FieldCount - 1)
                            sw.Write(separador);
                    }
                    sw.WriteLine();
                }

                if (string.IsNullOrEmpty(separador))
                {
                    while (exportData.Read())
                    {
                        for (var i = 0; i < exportData.FieldCount; i++)
                        {
                            sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")));
                            if (i < exportData.FieldCount - 1)
                                sw.Write(separador);
                        }
                        sw.WriteLine();
                    }
                }
                else
                {
                    var separadorTroca = new string(' ', separador.Length);

                    while (exportData.Read())
                    {
                        for (var i = 0; i < exportData.FieldCount; i++)
                        {
                            sw.Write(Convert.ToString(exportData.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")).Replace(separador, separadorTroca));
                            if (i < exportData.FieldCount - 1)
                                sw.Write(separador);
                        }
                        sw.WriteLine();
                    }
                }


                conn.Close();
                sw.Close();
                conn.Dispose();
                getOutput.Dispose();

            }

        }
        catch (Exception e)
        {
            sw.Close();
            throw new ApplicationException("Erro : " + e.Message);
        }
    }
};

Código fuente del procedimiento stpEscribe_Arquivo

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpEscreve_Arquivo(SqlString Ds_Texto, SqlString Ds_Caminho, SqlString Ds_Codificacao, SqlString Ds_Formato_Quebra_Linha, SqlBoolean Fl_Append)
    {


        if (!Ds_Texto.IsNull && !Ds_Caminho.IsNull && !Fl_Append.IsNull)
        {

            try
            {

                var dir = Path.GetDirectoryName(Ds_Caminho.Value);

                if (!Directory.Exists(dir))
                    Directory.CreateDirectory(dir);

            }
            catch (Exception e)
            {
                throw new ApplicationException("Erro : " + e.Message);
            }


            var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;
            if (Ds_Codificacao.Value.Trim() == "")
                encoding = "UTF-8";

            var sb = new StringBuilder(Ds_Texto.Value);

            var fileStream = new FileStream(Ds_Caminho.Value, ((Fl_Append) ? FileMode.Append : FileMode.Create));
            var sw = new StreamWriter(fileStream, Encoding.GetEncoding(encoding));

            switch (Ds_Formato_Quebra_Linha.Value.ToLower())
            {
                case "unix":
                    sw.NewLine = "\n";
                    sb.Replace("\r", "");
                    break;
                case "mac":
                    sw.NewLine = "\r";
                    sb.Replace("\n", "");
                    break;
                default:
                    sw.NewLine = "\r\n";
                    break;
            }


            try
            {

                var texto = sb.ToString();

                sw.Write(texto);
                sw.Close();

            }
            catch (Exception e)
            {
                sw.Close();
                throw new ApplicationException("Erro : " + e.Message);
            }

        }
        else
            throw new ApplicationException("Os parâmetros de input estão vazios");

    }
};

Ejemplo de uso:

-- Exportando uma string para arquivo texto
DECLARE @Texto VARCHAR(MAX) = 'Testando
Arquivo texto

com
quebra
de
linhas'

EXEC CLR.dbo.stpEscreve_Arquivo 
    @Ds_Texto = @Texto, -- nvarchar(max)
    @Ds_Caminho = N'C:\Temp\CLR_Texto.txt', -- nvarchar(max)
    @Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max)
    @Ds_Formato_Quebra_Linha = N'windows', -- nvarchar(max)
    @Fl_Append = 0 -- bit


-- Exportando query para arquivo texto (CSV)
EXEC CLR.dbo.stpExporta_Query_Txt 
    @query = N'SELECT * FROM sys.tables', -- nvarchar(max)
    @separador = N';', -- nvarchar(max)
    @caminho = N'C:\Temp\CLR_Teste.csv', -- nvarchar(max)
    @Fl_Coluna = 1 -- int

SQL Server - Export data to CSV with CLR (C#) Results — SQL Server: exporte datos a CSV con resultados de CLR (C#)

¡Eso es todo, amigos!
Hasta el próximo post.

servidor SQL exportar datos archivo de texto txt exportar datos archivos de texto desde la base de datos desde la base de datos

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…