Dynamic sql for convert table to flat file using bcp

Question

Post reply

Dynamic sql for convert table to flat file using bcp

Konstantin Taranov

SSCertifiable

Points: 6498
More actions
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

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

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

imarran SSC-Addicted Points: 489 More actions · Answer 1

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

pawel.sowka SSC Eights! Points: 851 More actions · Answer 2

SET @cmdCommand = 'del ' + @filePath;

Why output file is deleted?

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 3

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.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 4

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 21, 2015 at 6:55 am

#1799639

Thanks for this scripts. It can be very useful.

rw_ebox SSC Eights! Points: 804 More actions · Answer 6

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

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 7

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

Dynamic sql for convert table to flat file using bcp

Cookies on SQLServerCentral