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
 SQL Server Administration (2005)
 Use bcp to purge table. Format Files?

Author  Topic 

dbthj
Posting Yak Master

143 Posts

Posted - 2010-02-04 : 14:17:11
The question is about Format Files.
I have a couple of apps (written by in-house programmers) that require big yearly purges. They want to issue a huge delete statement that causes the log to grow very large. Even if it completes, it takes a very long time. I want to use bcp to unload the useful data, then truncate the table, then load the good data back in. My question is about format files. In my tests, the bcp process works just fine without using format files. I tested with a table that has varchar, integer and datetime columns.

My question is "under what circumstances do you need format files?"

Here is the process I used in my tests:

1. bcp "select * from DBNAME.SCHEMA.TBNAME where <condition>" queryout "<path>\<filename>" -c -U sa -S <servername>\<instance>
2. truncate table
3. Put DB in bulk-logged mode.
4. bcp DBNAME.SCHEMA.TBNAME in "<path>\<filename>" -c -U sa -S <servername>\<instance>

If you do need format files, can you get them without a lot of manual work? Seems like SQL Server knows what the table looks like. It ought to be able to generate a format file automagically without asking a bunch of questions.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-02-04 : 14:29:58
If you BCP the table out and back in in native mode, you will not need a format file.

You can read about the BCP command options in SQL Server Book Online.



CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-04 : 14:38:20
ok...

1. Why do you need to unload the table?

SELECT * INTO Backup_table FROM Table

Or just use a dump

2. What format is the data in That you want to load?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

dbthj
Posting Yak Master

143 Posts

Posted - 2010-02-05 : 10:21:43
Thanks for the replies. This is helpfull.
1. Why not use backup table?
I don't want to use a temporary table because a disk drive out of space due to database activity is a DBA's responsibility (mine) and any new tables in the production environment can mean one-off exceptions to standards (I'm part of a group with many database environments). On the other hand, data management is a programmer responsibility. The programmer can handle bcp without my involvement. And stand responsible for his own disk space usage.
2. Native data formats seem to be the way to go. I'll retest with -n
3. What format is the data I want to load?
I don't understand the question. See my original post for the commands I am using and the data types in the table. I will likely use the same process for other tables, the format of which I do not - at this point - know. What goes out comes back in.
4. If I should decide to use a format file, does anyone know how to get SQL Server to just make one without asking a lot of questions?

I used to be a DB2 guy. When you unloaded data there, DB2 created a format file with no user input. Seems like SQL could do that too if it wanted to. Can it?
Go to Top of Page
   

- Advertisement -