How to Create / Insert Table Data From Remote server in Sql Server 2005 to local server

OPENROWSET can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access.

To set DisallowAdhocAccess :-

Execute below script:

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO

RECONFIGURE;
GO

After that execute following statement

select * into Destination_table from

(SELECT a.*

FROM OPENROWSET('SQLOLEDB','SERVER';'USER';'PASSWORD',

'SELECT * FROM Database.dbo.SourceTableName') AS a ) a

first it will create Destination_table in our local server , and after it will copy all data from Remote server database "Database.dbo.SourceTableName" into Destination_table.