Need help on BCP

Question

Post reply

Need help on BCP

MuraliKrishnan1980

SSCrazy

Points: 2497
More actions
August 3, 2011 at 12:45 am

#245524

Hello Friends,
I am trying to use bcp command to export data between servers. This is my requirement. Data from Table1 in Server A is loaded to Table2 in Server B. Table 1 in Server A has say 6 columns(col1,col2,col3,col4,col5,col6). Table 2 in Server B has 7 columns(col1,col2,col3,col4,col5,col6,col7). I have to load the data from col2,col3,col4 in Table 1 to col4,col5 & col6 in Table 2. I know that i have to use a format file to implement this functionality. But could someone help me out to know what exactly are the changes that we have to make after creating the format file.
Thanks
Murali
August 3, 2011 at 1:30 am

This was removed by the editor as SPAM
August 3, 2011 at 7:17 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pujain SSCrazy Points: 2903 More actions · Answer 1

Another option is to use SSIS/DTS to transfer data.

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 2

Hello Friends,

Thanks for your response. Yeah, i know that we can achieve this using other options as well. However, i would like to know if there is an option to implement the requirement that i had mentioned using BCP.

Regards,

Murali

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

MuraliKrishnan1980 (8/3/2011)
Hello Friends,
Thanks for your response. Yeah, i know that we can achieve this using other options as well. However, i would like to know if there is an option to implement the requirement that i had mentioned using BCP.
Regards,
Murali

If you must use BCP have a look at this article for how to create a format file that can map data file fields to table columns that are in different orders:

Using a Format File to Map Table Columns to Data-File Fields

And also this one on how to skip data file fields using a format file:

Using a Format File to Skip a Data Field

Using the two techniques you should be able to create a format file that works for you.

Then:

1. BCP OUT of Server A to get a data file

2. BCP IN the file into Server B using the format file

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

stewartc-708166 (8/3/2011)
There are more efficient ways of transferring data between 2 SQL servers than using BCP out and in.
One option is to create a linked server on serverB that links to serverA, where you can do the insert / update inline, using the Merge command

This sounds an awful lot like you are saying to reference local tables and remote tables via Linked Server four-part naming in the same query using MERGE. For the benefit of future Googlers/readers, do not implement this technique. This could needlessly cause entire tables to be transferred between the two servers and could lead to messy performance issues.

In a later post you talk about moving data into a destination staging table and then issuing the MERGE on the remote server. That's a definite improvement, where the staging table is only populated with the subset of data necessary to get desired results after the MERGE. In this scenario I would recommend this flow:

1a. If the remote staging table will not previously exist: Use EXEC() AT LinkedServer to create a concrete remote staging table. Concrete meaning not a # or ## temp table.

1b. If the remote staging table will previously exist: Use EXEC() AT LinkedServer to ensure the remote staging table is clear.

2. Use an INSERT INTO LinkedServer.Database.RemoteSchema.RemoteTable...SELECT where LinkedServer.Database.RemoteSchema.RemoteTable is the remote table referenced using four-part naming and the SELECT only involves local tables, preferably with a WHERE clause that ensures only necessary data is sent to the remote table.

3. Use EXEC() AT LinkedServer to issue the MERGE at the remote server.

4. Optional: Use EXEC() AT LinkedServer to clear the remote staging table.

My apologies stewartc-708166 if this is what you meant in your first post, but your first post reads as poor advice.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

You might be able to get away without any format files if using BCP. Views work [font="Arial Black"]very [/font]nicely for such a thing in most cases. Of course, BCP format files will certainly do the trick and with great speed, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 6

Thanks for your response.

Yes, i tried this option and its working fine without any issues. But i have to move 34 tables from source to destination. So i believe it would be a tedious process to update the format file for every table.

Moreover, we are trying to achieve this requirement by calling a stored procedure and implementing the bcp in the stored procedure which means that updating the format files would not be possible.

The main issue i face is the column mismatch in the data file & the destination table and i know that the only way to resolve it is to update format file and reset the column for which data is not present in the data file.

Any other way to achieve it??Basically the column in the destination table for which the value is not imported is the identity field. Any other options available like bulk insert????