September 18, 2002 at 10:10 am
Hello everyone, I have several HUGE tables that we want to archive. I used DTS to make a flat file of each table, but it takes very very long. Can you recommend a faster way? Thanks.
September 18, 2002 at 10:13 am
You can use BCP to output tables to flat files. However, as I not played with BCP too much, I'd say check out BOL...or maybe someone else can give you a command syntax!
Clive Strong
September 18, 2002 at 1:21 pm
Not sure BCP is much faster or it's worth testing. If are are archiving, this is a one time event, correct? Does the time matter that much?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 18, 2002 at 9:19 pm
What SQLMAg says is that bcp is the fastest way to export data, and Bulk Insert the fastes for import.
September 19, 2002 at 6:04 am
I use BCP to export AND Import data to flat files because I can generate the scripts and save them to a .bat file and run them with a scheduled task. I generate bcp commands for all tables in a DB like so:
select 'PRINT '''+NAME+'''
exec xp_cmdshell ''bcp <ServerName>.dbo.'+name+' out "<FilePath>'+name+'.txt" -S <ServerName> -t<delimiter> -c -U <User> -P<Password>''
go' from sysobjects where TYPE = 'U' ORDER BY NAME
I know someone will probably say that a DTS Package can do the same. From my experience BCP works faster and has less steps (therefore less room for failure).
"It makes me want to run around the Server Room in a Super-Hero Costume"
"It makes me want to run around the Server Room in a Super-Hero Costume"
September 19, 2002 at 6:59 pm
That's true. I also prefer BCP to export data. Or if you want to make it really easy, run DTSWiz to import and export with a wizard
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply