Re: BCP Nightly Extracts
From: Erland Sommarskog (sommar_at_algonet.se)
Date: 03/16/04
- Next message: Tom Moreau: "Re: Help with Blocking"
- Previous message: Hugo Kornelis: "Re: Using CASE statement to build different where clause"
- In reply to: bc: "BCP Nightly Extracts"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 16 Mar 2004 23:51:20 +0000 (UTC)
bc (bkc98@excite.com) writes:
> I have currently existing code that exports my entire SQL Server 2000
> application database to many extract files each night. The process is
> made up of a series of custom DTS packages that splits up the extracts
> by table. The tables that are too large for one file are split up by
> id ranges (i.e. catalog_id). The extracts seem to work okay, but we
> would like to get rid of DTS and move to the faster BCP utility.
>
> I'm having trouble figuring out how to create extract files in the
> same format as our current extracts (we currently use the file extract
> step in DTS). The format of the current extract fiels look like this:
> ************************************************
> "brand_id"|"product_line_id"|"brand_name"|"brand_description"
> 1|1|"foo brand name"|"foo brand description1"
> 2|1|"foo brand name2"|""
> ************************************************
> Note: if a char field is null, it's noted as a ""
>
> I would like to keep the column headings if possible when using BCP. I
> don't want to create a format file for EACH custom extract to make it
> look like the above sample of my current extract files.
To include the column header as part of the file, you would need to
use a query which produces the column headers as data:
SELECT '"brand_id', '"product_line"', ...
UNION
SELECT ...
FROM tbl
But without a column to order on, there is not really any guarantee that
the headers will come first in the file. Another alternative is to prepend
the header afterwards, hut that is not likely to be efficient.
Unless the quotes are part of the data, you need to use a format file
to get the quotes. But as long as the format is the same for all files,
you only need one format file.
-- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
- Next message: Tom Moreau: "Re: Help with Blocking"
- Previous message: Hugo Kornelis: "Re: Using CASE statement to build different where clause"
- In reply to: bc: "BCP Nightly Extracts"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
