Replicating data loaded with BCP or Bulk Insert

Yes you can use both Transactional and Merge Replication to move data inserted using the BCP.exe utility of the Bulk Insert command to Subscribers.

Considerations For Transactional Replication

By default, Transactional Replication will replicate data loaded via BCP or Bulk Insert.  For performance, you may want to load data using the bcp Utility –b batch_size parameter set from 2,000 to 5,000.  This will allow the LogReader to move to the Distribution Database inserted commands in blocks/batches of 2,000 (5,000).  The Distribution Agent intern saves changes at the Subscribers in smaller blocks.  Smaller blocks/batches of data reduce locking.  You’ll also find it easier to track progress in Replication Monitor.

Another option is to use the LogReader -MaxCmdsInTran 2000 to have the LogReader group in batches of 2000 insert cmds.  Again you can experiment to see what value provides best performance.

If you are bulk loading millions of rows it may be faster to disable replication, load the data, reload subscriber using backup/restore, then re-enable replication as compared to moving individual inserts via Replication.

Considerations For Merge Replication

Merge Replication requires an additional step to move data to the subscribers.  Since Merge picks up data via Table Triggers, you need to either 1) fire the trigger on insert or 2) mark inserted rows to be replicated.

To bulk-load data into tables published by merge replication using the bcp utility

1. 1. At either the Publisher or Subscriber, execute the bcp Utility or BULK INSERT to insert data into a table published using merge replication.
   2. Use one of the following methods to ensure that replication metadata is generated for the inserted data.
      • • Execute the bulk copy using the FIRE_TRIGGERS option.
        • On the database into which data was inserted, execute sp_addtabletocontents (Transact-SQL). Specify the table name into which the data was inserted for @table_name.