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 - 2011-11-07 : 23:45:40

Dear Member

I have write a following bcp command to export all table in csv file format in Database and shedule it daily execute

SELECT @TableName=MIN(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE ='BASE TABLE'
WHILE @TableName IS NOT NULL
BEGIN
set @sql = 'bcp db..' + @TableName + ' ' + 'out E:\bcp\' + @TableName + '.csv -c -t, -T -S '

exec master..xp_cmdshell @sql

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

end

The process is work successfully. but in csv file only first 1000 record exported not all records
so what can i change in script

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 00:24:14

you mean 1000 records for all tables?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-11-08 : 00:29:01

thanks to replay
i have exported all record to all table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 00:37:58

quote:
Originally posted by amirs

thanks to replay
i have exported all record to all table

just to benefit others, can you specify what the problem was?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-11-08 : 00:42:44

in exported csv file only see first 1000 record of table but i want all records in table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-08 : 00:50:06

and how do you resolve this ?

KH
[spoiler]Time is always against us[/spoiler]

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-11-08 : 01:09:12

i am not resolve this i want solution,
what i can change in my script.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 02:17:35

quote:
Originally posted by amirs

i am not resolve this i want solution,
what i can change in my script.

you only posted right you exported all records?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-11-08 : 02:26:23

all records to all table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 03:35:09

its really confusing...are you telling problem still persists?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-11-08 : 03:49:01

In one of my database i need to export all tables in individuals csv files

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 04:34:33

then look for bcp

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources