Post reply

Dynamic sql for convert table to flat file using bcp

  • May 7, 2015 at 6:44 pm

    #398427

    Comments posted to this topic are about the item Dynamic sql for convert table to flat file using bcp

  • May 21, 2015 at 2:21 am

    #1799560

    Some nice code and a useful tool 🙂

    Will need to replace the "THROW" command with RAISERROR to make it work on older versions of SQL Server

  • May 21, 2015 at 2:22 am

    #1799561

    SET @cmdCommand = 'del ' + @filePath;

    Why output file is deleted?

  • May 21, 2015 at 2:35 am

    #1799566

    pawel.sowka (5/21/2015)

    SET @cmdCommand = 'del ' + @filePath;

    Why output file is deleted?

    Hello pawel.sowka, thanks for your question.

    Output file deleted only if parameter @outputColumnHeaders = 1 (see below if expression) because in this case I created two files: first with data (named like @tableFullName) and second with column headers (named like @tableFullName + '_headers'), and then merge it with cmd copy command into headers file (second). After that I deleted unnecessary first file.

  • May 21, 2015 at 2:42 am

    #1799567

    imarran (5/21/2015)

    Some nice code and a useful tool 🙂

    Will need to replace the "THROW" command with RAISERROR to make it work on older versions of SQL Server

    Thanks for your comment. I regular use this procedure for quick migration between database (bulk insert for import). If you find some errors or improvements fill free to create issue on github: https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_bcpTableUnload.sql

  • May 21, 2015 at 6:55 am

    #1799639

    Thanks for this scripts. It can be very useful.

  • May 21, 2015 at 9:49 am

    #1799745

    Surprise, people still use bcp. I love this script.

  • May 22, 2015 at 4:40 am

    #1799950

    rw_ebox (5/21/2015)

    Surprise, people still use bcp. I love this script.

    Thanks for your reply. For my opinion and practice bcp the best solution for quick export big tables (millions rows and more) in flat file. I love autohotkey (http://www.autohotkey.com/) macros and regular use it for fast script generation. For this procedure it looks:

    ^!#2::

    InputBox, replaceString, Replace String, usp_bcpTableUnload: Please input Table name

    if ErrorLevel

    {

    MsgBox, CANCEL was pressed.

    return

    }

    searchString = ___TableName___

    TSQLStatetment =

    (

    EXECUTE [dbo].[usp_bcpTableUnload]

    @path = 'd:\'

    , @databaseName = 'NIIGAZ'

    , @schemaName = 'dbo'

    , @tableName = '___TableName___'

    , @fieldTerminator = '|'

    , @fileExtension = 'txt'

    , @excludeColumns = '[CreatedDate],[ModifiedDate],[UserID]'

    , @orderByColumns = '___TableName___ID'

    , @outputColumnHeaders = 1

    , @debug = 0;

    )

    StringReplace, TSQLStatetment, TSQLStatetment, %searchString%, %replaceString%, All

    Clipboard = %TSQLStatetment%

    Send ^v

    return

    For quick backup single database:

    ^!#1::

    InputBox, replaceString, Replace String, Databse backup: Please input Database name

    if ErrorLevel

    {

    MsgBox, CANCEL was pressed.

    return

    }

    searchString = ___DatabaseName___

    BackupString =

    (

    BACKUP DATABASE [___DatabaseName___] TO DISK = N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\___DatabaseName___.bak'

    WITH NOFORMAT

    ,NOINIT

    ,NAME = N'___DatabaseName___-Full Database Backup'

    ,SKIP

    ,NOREWIND

    ,NOUNLOAD

    ,COMPRESSION

    ,STATS = 10;

    GO

    DECLARE @backupSetId AS INT;

    SELECT @backupSetId = position

    FROM msdb..backupset

    WHERE database_name = N'___DatabaseName___'

    AND backup_set_id = (

    SELECT max(backup_set_id)

    FROM msdb..backupset

    WHERE database_name = N'___DatabaseName___');

    IF @backupSetId IS NULL

    BEGIN

    RAISERROR (N'Verify failed. Backup information for database ''___DatabaseName___'' not found.', 16 ,1);

    END

    RESTORE VERIFYONLY

    FROM DISK = N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\___DatabaseName___.bak'

    WITH FILE = @backupSetId

    ,NOUNLOAD

    ,NOREWIND;

    GO

    )

    StringUpper replaceString, replaceString

    StringReplace, BackupString, BackupString, %searchString%, %replaceString%, All

    Clipboard = %BackupString%

    Send ^v

    return

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply