RE: How can I insert in a single table from different SQL Servers

SSCrazy

Points: 2143

May 2, 2018 at 2:25 am

#1988896

anusql74 - Monday, April 16, 2018 9:00 AM
I am trying to create MSX ans TSX relationship
MSX is SQL1
TSX is
SQL2 and SQL3
created a job in SQL1 that will run on SQL2 and SQL3
job step is :
create table #sqltest_temp (sqlversion nvarchar(max))
use master
go
insert into #sqltest_temp Select @@SERVERNAME
go
insert into [SQL1].[Databasename].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
go
drop table #sqltest_temp
But when I querry this table [SQL1].[Databasename].[dbo].[sqlversion_anu] it shows only one value whereas should show value for both SQL2 and SQL3 , anyone ...where I am missing ?

One way to do this is to use OPENQUERY for Linked Servers. For example, creating an Agent Job on SQL1 with the following code might work.

USE TempDB;

create table #sqltest_temp (sqlversion nvarchar(max))

insert into #sqltest_temp
select * from table
union
select * from openquery(SQL2,'Select @@SERVERNAME')
union
select * from openquery(SQL3,'Select @@SERVERNAME')

insert into [DatabaseName].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
GO

drop table #sqltest_temp

=======================================================================