0

how can i copy data from a view of one server into the database table of another server in a SQL server database without using linked servers method. I tried using the code:

SELECT [LogEntryID]
      ,[TimeStamp]
      Into [server-2].[database1].[dbo].[table1]
  FROM [server-1].[database1].[dbo].[view_1]

I recieve the error " The object name 'server-2.database1.dbo.table1' contains more than the maximum number of prefixes. The maximum is 2. The copying statement i am using in a groovy code. But for first i am trying it in a query if it works.

user9630935
  • 349
  • 1
  • 4
  • 18
  • what about insert into table1 from view_1 does that give you the same error? – Harry Jun 18 '18 at 09:12
  • I used INSERT Into [server-2].[database1].[dbo].[table1] SELECT * FROM [server-1].[database1].[dbo].[view_1] . I got an error Could not find server 'server-1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. It can be done using linked servers but i don't want to use one. – user9630935 Jun 18 '18 at 09:19

1 Answers1

0

You can use opendatasource instead of linked server

insert DestinationTable
select * from opendatasource('SQLOLEDB', 'Server=SourceServer;User Id=Username;Password=Password;').SourceDb.Schema.SourceTable;

OPENDATASOURCE

Dmitry Kolchev
  • 2,116
  • 14
  • 16