Transfer Data one server to another

http://okayguru.blogspot.co.uk/2012/03/copy-one-database-to-another-database.html

There are several ways to do this, below are two options:

Option 1 - Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish

INSERT INTO dbo.YourTableNameHere   
 SELECT * FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere

try this. It's work for me.