Couple Solutions to Transfer Data Between Database

1 Solution: Import/Export (Using SSIS,bulk insert )

 

 

2 Replication Service (e.g: Snapshot Replication : Using BCP)

 

Workflow: drop the table and recreate and then bulk copy in the data. The level of locking you will see will be similar to when you bcp in data into a table,snapshot files has been saved to %temp%

 

http://sqlblog.com/blogs/jorg_klein/archive/2011/03/12/replication-services-as-etl-extraction-tool.aspx

In my last blog post I explained the principles of Replication Services and the possibilities it offers in a BI environment. One of the possibilities I described was the use of snapshot replication as an ETL extraction tool: 
“Snapshot Replication can also be useful in BI environments, if you don’t need a near real-time copy of the database, you can choose to use this form of replication. Next to an alternative for Transactional Replication it can be used to stage data so it can be transformed and moved into the data warehousing environment afterwards. 
In many solutions I have seen developers create multiple SSIS packages that simply copies data from one or more source systems to a staging database that figures as source for the ETL process. The creation of these packages takes a lot of (boring) time, while Replication Services can do the same in minutes. It is possible to filter out columns and/or records and it can even apply schema changes automatically so I think it offers enough features here. I don’t know how the performance will be and if it really works as good for this purpose as I expect, but I want to try this out soon!” 

Well I have tried it out and I must say it worked well. I was able to let replication services do work in a fraction of the time it would cost me to do the same in SSIS. 
What I did was the following:

1. Configure snapshot replication for some Adventure Works tables, this was quite simple and straightforward.
2. Create an SSIS package that executes the snapshot replication on demand and waits for its completion. 
   This is something that you can’t do with out of the box functionality. While configuring the snapshot replication two SQL Agent Jobs are created, one for the creation of the snapshot and one for the distribution of the snapshot. Unfortunately these jobs are  asynchronous which means that if you execute them they immediately report back if the job started successfully or not, they do not wait for completion and report its result afterwards. So I had to create an SSIS package that executes the jobs and waits for their completion before the rest of the ETL process continues.

Fortunately I was able to create the SSIS package with the desired functionality. I have made a step-by-step guide that will help you configure the snapshot replication and I have uploaded the SSIS package you need to execute it.

.....