Transfer data from one server to another server

Using the following query you can transfer data from one server to another server. Here both source and destination is SQL Server.

#####################Start Query#####################3

insert into OPENDATASOURCE('SQLOLEDB', 'Data Source=mssql2005;User ID=dnn500;Password=dnn500').dnn500.dbo.Brochures
(Title, ShortDescription, ValidTo, ImageName, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate)

SELECT Title, ShortDescription, ValidTo, ImageName, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=mssql2005;User ID=etravelqa;Password=etravelqa').etravelqa.dbo.Brochures

#####################End Query#####################

The query can also be used to transfer data, where source or destination can be any other datasource.

For example, here datasource is Excel file.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;