Data Population from One Server to Another Server?

Question

Data Population from One Server to Another Server?

Hakuna Matata

SSCertifiable

Points: 5577
More actions
April 14, 2011 at 3:51 am

#237610

Hi
How can I Insert Data from one or more tables in Database DB1 present on Server DBSERVER1 to another database DB2 present on a Different server DBSERVER2.
Regards,
Nithin
April 14, 2011 at 7:04 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 1

Our Current Production Server runs on SQL Server 2008 R2 Enterprise Edition. We want to implement a separate Report Server & Tables on the report server will be populated based on reports. Hence at the end of day I have to run a query that will get data(report data) from the tables on production server & append to the report server table.

We plan to denormalize data from the production server & put all the data required for a particular report into a single table.

For Example If we want to generate invoice report . All I need to do is pull data from the InvoiceReport Table on the Report Server which will be populated using data from the Tables on the Production Server.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

kr.nithin (4/14/2011)
Our Current Production Server runs on SQL Server 2008 R2 Enterprise Edition. We want to implement a separate Report Server & Tables on the report server will be populated based on reports. Hence at the end of day I have to run a query that will get data(report data) from the tables on production server & append to the report server table.
We plan to denormalize data from the production server & put all the data required for a particular report into a single table.
For Example If we want to generate invoice report . All I need to do is pull data from the InvoiceReport Table on the Report Server which will be populated using data from the Tables on the Production Server.

This is normally data move on daily basis on a large database.

Insert into [Server2].DBName.dbo.[TableName] /*Assuming as Report server */

Values (Col1,Col2,Col3)

Select Col1,Col2,Col3 from [Server1].DBName.dbo.[TableName]

Where <Condition>

/*Only necessary fields with necessary data*/

for the above you need to create Linked server

http://msdn.microsoft.com/en-us/library/ms188279.aspx

After this you can point out server2 for reports or for some other purpose.

Thanks
Parthi

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 3

Thanks a lot for the prompt reply. Will try implementing as advised.:-)