Bulk Copy from one server to another

Question

I've one situation where I need to copy part of the data from one server to another. The table schema are exactly same. I need to move partial data from the source, which may or may not be available in the destination table. The solution I'm thinking is, use bcp to export data to a text(or .dat) file and then take that file to the destination as both are not accessible at the same time (Different network), then import the data to the destination. There are some conditions I need to satisfy:

1. I need to export only a list of data from the table, not whole. My client is going to give me IDs which needs to be moved from source to destination. I've around 3000 records in the master table, and same in the child tables too. What I expect is, only 300 records to be moved.
2. If the record exists in the destination, the client is going to instruct as whether to ignore or overwrite case to case. 90% of the time, we need to ignore the records without overwriting, but log the records in a log file.

Please help me with the best approach. I thought of using BCP with query option to filter the data, but while importing, how do I bypass inserting the existing records? How do I overwrite, if that is needed?

Answer 1

Unfortunately BCPing into a table is an all or nothing deal, you can't select rows to bring in.

What I'd do is . . .

1. Create a table on the source database, this will store the ID's of the rows you need to move. You can now BCP out the rows that you need.
2. On the destination database, create a new Work In Progress table, and BCP the rows in there.
3. Once in there you can write a script that will decide whether or not a WIP row goes into the destination table, or not.

Hope this helps.

Update

By work in progress (WIP) tables I don't mean #temp tables, you can't BCP into a temp table (at least I'd be very sprprised if you could).
I mean a table you'd create with the same structure of the destination table, bcp into that, script the WIP rows to the destination table then drop the WIP table.

You haven't said what RDBMS you're using, assuming SQL Server, something like the following (untried code) . . .

-- following creates new table with identical schema to destination table
select * into WIP_Destination from Destination
where 1 = 0

-- BCP in the rows
BULK INSERT WIP_Destination from 'BcpFileName.dat'

-- Insert new rows into Destination
insert into Destination
Select * from WIP_Destination 
where not id  in (select id from Destination)

-- Update existing rows in destination

Update Destination 
set field1 = w.field1, 
    field2 = w.field2, 
    field3 = w.field3, 
    . . . 
from Destination d inner join WIP_Destination w on d.id = w.id

Drop table WIP_Destination

Update 2
OK, so you can insert into temporary tables, I just tried it (I didn't have time the other day, sorry).

On the problem of the master/detail records (and we're now moving off the subject of the original question, if I were you I'd open a new question for this topic, you'll get more answers than just mine)

You can write an SP that will step through the new rows to add.
So, you're looping through the rows in your temp table (these rows have the original id on them from the source database), insert that row into the Destination table, use SCOPE_IDENTITY to get the id of the newly inserted row. Now you have the old Id and the new ID, you can create an insert statement that will insert statement for the detail rows like . . .

insert into Destination_Detail
select @newId, field1, field2 . . . from #temp_Destination_Detail
where Id = @oldId

Hope this helps [if it has helped you are allowed to upvote this answer, even if it's not the answer you're going to select :)]

Thanks
BW