export table use BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

export table use BCP

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2012-01-02 : 05:17:47

Dear Members

Is one of my database i have export all tables in csv file
i have write and schedule a following bcp script .

Create PROCEDURE [dbo].[bcp_proc]

as

DECLARE @TableName sysname
DECLARE @sql VARCHAR(4000)
declare @cmd varchar(200)

SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'
WHILE @TableName IS NOT NULL

BEGIN

set @sql = 'bcp baanenvdb..' + @TableName + ' ' + 'out E:\dbname\bcp\' + @TableName + '.csv -c -t, -T -S '
exec master..xp_cmdshell @sql

WAITFOR DELAY '00:00:02'

SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'
AND TABLE_NAME > @TableName

END

it execute is ok but the task of complete this process is near about 16 hours .In database 20000 table are available and 6000 table are blanks,the backup file size of database is 110 GB and exported table csv file size is 68 GB.

so what i can change in this script to improve the performance ,

please tell me any other way to export all tables in CSV file.

I have use sql2005 SP2

thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-02 : 08:26:34

What is the purpose of the WAITFOR DELAY '00:00:02'? That causes the code to pause for 2 seconds before each file is processed. With 20,000 files that is more than 11 hours. Unless there is a very good reason you want it that way, I would remove that statement and try again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-02 : 12:20:57

concurrency access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources