BCP to a table partition

Can any one tell me how to BCP to perticular partition of the table in SQL Server.

You have to know the boundary values specified and select the data that via bcp would enter of your partition.

As we see a BOL example :

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime) AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401', '20030501', '20030601', '20030701', '20030801', '20030901', '20031001', '20031101', '20031201'); you can select the data from a desired month before use bcp to introduct data in one of this 12 partitions.

So, your problem should be selecting data.

|||

Ok. But am not getting the proper syntax to use BCP.

In the above example, the first partition would contain the records having date range 20030201 - 20030301.

For example if i want to do BCP to this partition how do i do it.

Please explain with an example (Syntax).

|||

I'm based on Books Online for explanation :

First the partition have to be built as BOL say

The syntax of bcp is explained in BOL too.

if we run

bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c

bcp will insert all the data from Currecy.dat file in AdventureWorks.Sales.Currency2 table

the process of inserting data in the partitions is transparent for user and is based on that boundary value.

So , to insert data in that partition the Currecy.dat file have to be built something like this

ID AnyData Etc..

1 20030215 hjhjhj

2 20030215 lftdfhh

3 20030216 yuhjhq2kn

|||

thanks.

I want to do like this.

i want to run different BCPs in parallel where each BCP will have the inputfile having the data ,going to perticular partition of the same table.

For ex:

First thread BCP will have file1 having all the data going to partition 1

second thread BCP will have file2 having all the data going to partition 2

.

.

.

etc..

But i feel that if it supports like this, We will have overhead where BCP would be comparing the value passed and comparing it and then loading it to perticular partition.

But in Oracle we can tell the SQL loader that load in to the perticular partition. SO that its easier for the loader to load the data without any other computation

|||

Looking for bcp utility [SQL Server], performance in BOL i read:

"Where possible, use a Transact-SQL statement to bulk import data into SQL Server because Transact-SQL is faster than bcp." so maybe you can consider using bulk insert command.

Read "About Bulk Import and Bulk Export Operations." that is an item that show a comparison from bulk insert methods from performance point of view.

I think the SQL engine move the data in appropriate partition not bcp; the overhead can be saved by the power of the machine.