Best Way to BCP

8 Jul 2005 7:44 PM

Crazy about SQL Server

What is the best way to BCP data out of one table? Should I move the data I
need into a staging table, then BCP the data out of there? Or use the query
directly in the BCP command? I have a WHERE clause in the query using one of
two values that make up the primary key on the table. Thank you.

8 Jul 2005 8:20 PM

Ravi

You can use query in bcp
like
bcp "select * from table where col1='filtervalue' queryout c:\tabledata.txt

--
Thanks & Rate the Postings.
-Ravi-

Show quote

"Crazy about SQL Server" wrote:

> What is the best way to BCP data out of one table? Should I move the data I
> need into a staging table, then BCP the data out of there? Or use the query
> directly in the BCP command? I have a WHERE clause in the query using one of
> two values that make up the primary key on the table. Thank you.

8 Jul 2005 8:28 PM

Crazy about SQL Server

Thanks for the reply. But I'm wondering what the best way would be to BCP
data. I know how to BCP the data, but is it best to query the table
directly? Or move the data into a staging table then BCP the data out? This
is an OLTP production system and I don't want to negatively affect anything
running on the server.

When I ran the query to insert into a staging table, CPU usage went to 80%.
I want to try to avoid that in the future, if possible. So I'm wondering if
a direct BCP query would work better or make things worse.

Thanks.

Show quote

"Ravi" wrote:

> You can use query in bcp
> like
> bcp "select * from table where col1='filtervalue' queryout c:\tabledata.txt
> --
> Thanks & Rate the Postings.
> -Ravi-
>
>
> "Crazy about SQL Server" wrote:
>
> > What is the best way to BCP data out of one table? Should I move the data I
> > need into a staging table, then BCP the data out of there? Or use the query
> > directly in the BCP command? I have a WHERE clause in the query using one of
> > two values that make up the primary key on the table. Thank you.

8 Jul 2005 9:16 PM

Ravi

definitely direct bcp is better than table insert.

--
Thanks & Rate the Postings.
-Ravi-

Show quote

"Crazy about SQL Server" wrote:

> Thanks for the reply. But I'm wondering what the best way would be to BCP
> data. I know how to BCP the data, but is it best to query the table
> directly? Or move the data into a staging table then BCP the data out? This
> is an OLTP production system and I don't want to negatively affect anything
> running on the server.
>
> When I ran the query to insert into a staging table, CPU usage went to 80%.
> I want to try to avoid that in the future, if possible. So I'm wondering if
> a direct BCP query would work better or make things worse.
>
> Thanks.
>
> "Ravi" wrote:
>
> > You can use query in bcp
> > like
> > bcp "select * from table where col1='filtervalue' queryout c:\tabledata.txt
> > --
> > Thanks & Rate the Postings.
> > -Ravi-
> >
> >
> > "Crazy about SQL Server" wrote:
> >
> > > What is the best way to BCP data out of one table? Should I move the data I
> > > need into a staging table, then BCP the data out of there? Or use the query
> > > directly in the BCP command? I have a WHERE clause in the query using one of
> > > two values that make up the primary key on the table. Thank you.

8 Jul 2005 9:28 PM

JT

Optimize the query you are using with bulk copy so that it takes the least
time to complete, this includes seeking on an indexed column. Also, consider
using the NOLOCK or READPAST locking hints. NOLOCK does not acquire or honor
shared locks. This allows for more consurrency with other users of the
table, but also allows the possibility of a dirty read from an uncommitted
transaction. The READPAST locking hint specifies that locked rows are simply
skipped and excluded from your query. Whether either of these two options
are desireable depends on your purpose for copying from the table. For some
types of reporting, having a small percentage of dirty or excluded results
does not cause the results to fall outside the acceptable margin of error.

Show quote

"Crazy about SQL Server" <CrazyaboutSQLSer***@discussions.microsoft.com>
wrote in message news:D9877E6F-EC22-4454-A75A-E6222F5213BE@microsoft.com...
> Thanks for the reply. But I'm wondering what the best way would be to BCP
> data. I know how to BCP the data, but is it best to query the table
> directly? Or move the data into a staging table then BCP the data out?
This
> is an OLTP production system and I don't want to negatively affect
anything
> running on the server.
>
> When I ran the query to insert into a staging table, CPU usage went to
80%.
> I want to try to avoid that in the future, if possible. So I'm wondering
if
> a direct BCP query would work better or make things worse.
>
> Thanks.
>
> "Ravi" wrote:
>
> > You can use query in bcp
> > like
> > bcp "select * from table where col1='filtervalue' queryout

c:\tabledata.txt
Show quote

> > --
> > Thanks & Rate the Postings.
> > -Ravi-
> >
> >
> > "Crazy about SQL Server" wrote:
> >
> > > What is the best way to BCP data out of one table? Should I move the
data I
> > > need into a staging table, then BCP the data out of there? Or use the
query
> > > directly in the BCP command? I have a WHERE clause in the query using
one of
> > > two values that make up the primary key on the table. Thank you.