Technical Article

Bulk Copy All?Table Data into formatted files


Before you run the SQL script;

1. Create a folder on the server named: driveLetter\databasename

2. Enable xp_cmdshell by running sp_configure

3. Change fully qualified databasename for example: databasename.dbo.sysobjects to yourdatabasename.dbo.sysobjects


Hope this helps in bulk copying table data into formatted file...

set nocount on

/* Initialize variables here */declare @TableNames table (TableTempID  
smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(50))
declare @TableName varchar(50)
declare @BackupFileName char(50)
declare @BackupFolderFile varchar(150)
declare @Counter smallint
declare @MaxTableCount smallint
declare @BackupFolder varchar(100)
declare @BCPOutCommand varchar(1000)

/* Set BackupFolder name here */set @BackupFolder = 'driveletter:\databasename\'

/* Get the list of tables that we want to backup */insert into @TableNames (TableName) 
select name from 
where xtype = 'U' 
and name not in ('dtproperties')

select @MaxTableCount = max(TableTempID) from @TableNames
set @Counter = 1

/* Loop through all each table individually, generate bcp 
commands and run bcp commands to export data */while @Counter <= @MaxTableCount


/* Create backup file name */select @TableName = ltrim(rtrim(TableName)) from 
@TableNames where TableTempID = @Counter

select @BackupFileName = ltrim(rtrim(@TableName))-- + '.txt'

/* Combine backup folder name and file name */select @BackupFolderFile = @BackupFolder + @BackupFileName

/* Create BCP command */select @BCPOutCommand = 'bcp ' + 'DatabaseName.dbo.' + @TableName + 
' out ' + ltrim(rtrim(@BackupFolderFile)) + 
' -q -c -S' + convert(varchar, serverproperty('servername')) + ' -T'

--print @BCPOutCommand
exec master..xp_cmdshell @BCPOutCommand
set @Counter = @Counter + 1


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating