SQLTeam.com | Weblogs | Forums

Insert or Update a record via Openrowset linked server

Hi Experts !
I am trying to insert into table from stored procedure using openrow set and till now i got success ,but i required not to drop the table and re insert the data via stored procedure , the Openrowset /linked server works with both cases if record already exists then update if not insert ... I try much via google but no luck..
may be it can work using merged function? please help me

DROP TABLE IF EXISTS TableCd;
GO	
 SELECT  StartDate = CONVERT(DATETIME     ,v.StartDate)
        ,ItemName  = CONVERT(VARCHAR(1000),v.ItemName)
        ,Data1     = CONVERT(FLOAT        ,v.Data1)
        ,Data2     = CONVERT(FLOAT        ,v.Data2)
   INTO TableCd
   FROM (VALUES
         ('10-Oct-2021','ItemName1',1336.9 ,0.85623)
        ,('10-Oct-2021','ItemName2',318.62 ,0.31913)
        ,('10-Oct-2021','ItemName3',40     ,50     )
        ,('10-Oct-2021','ItemName4',1068.51,7.23771)
        ,('10-Oct-2021','ItemName5',238.51 ,3.56735)
        ,('11-Oct-2021','ItemName1',10     ,20     )
        ,('11-Oct-2021','ItemName2',11     ,21     )
        ,('11-Oct-2021','ItemName3',12     ,22     )
        ,('11-Oct-2021','ItemName4',13     ,23     )
        ,('11-Oct-2021','ItemName5',14     ,24     )
        ,('12-Oct-2021','ItemName1',10     ,20     )
        ,('12-Oct-2021','ItemName2',11     ,21     )
        ,('12-Oct-2021','ItemName3',12     ,22     )
        ,('12-Oct-2021','ItemName4',13     ,23     )
        ,('12-Oct-2021','ItemName5',14     ,24     )
        )v(StartDate,ItemName,Data1,Data2)
;

Select * from TableCd



Create or alter Procedure TestSP ---The purpose of create stored procedure to run complex calculation faster
																	@StartDate datetime output,
																	@EndDate datetime output

AS

BEGIN
select StartDate,ItemName,Data1,Data2  from  TableCd
where StartDate>=@StartDate and StartDate<=@EndDate
end
Exec TestSP '10/11/2021' , '10/12/2021'


sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO




EXEC sp_addlinkedserver @server='Servername\SQLSERVER2017'     --- change to your server and instance name
EXEC sp_addlinkedsrvlogin 'SQLSERVER2017', 'false', NULL, 'sa', 'password'  ---change to your  user and password

select * from sys.sysservers where srvname = 'Servername\SQLSERVER2017'  --- change to your server and instance name

SELECT * FROM [Servername\SQLSERVER2017].master.sys.databases    --- change to your server and instance name

USE master
IF OBJECT_ID('tempdb.dbo.#TempTable3', 'U') IS NOT NULL
DROP TABLE #TempTable3;         
SELECT *
INTO #TempTable3
---FROM OPENROWSET('SQLNCLI', 'Server=Servername\SQLSERVER2017;Trusted_Connection=yes;','EXEC Codelist.[dbo].[TestSP] ''10/01/2021'', ''10/11/2021'' ')
FROM OPENROWSET('SQLNCLI', 'Server=Servername\SQLSERVER2017;Uid=sa;Pwd=password;Database=Codelist;Trusted_Connection=yes;',   --- change to your server and instance name/user and password
' set fmtonly off;
SET NOCOUNT ON;
DECLARE	@StartDate datetime,
		@EndDate datetime
SET	@StartDate = ''10/1/2021''    ---  @Productionday  
SET	@EndDate =  ''10/11/2021''     ---- @ProductiondayE 
EXEC  Codelist.[dbo].[TestSP] @StartDate, @EndDate
')
GO

SELECT * FROM #TempTable3 WHERE StartDate> = '10/1/2021'  ---  @Productionday  	 
					 AND      [StartDate]<= '10/11/2021'---- @ProductiondayE

please define the above. what makes a record already exists? and which are you trying to work with ? TableCd?

Hi Yosiasz,

  1. The sample table TableCd ( there are multiple tables like TableCD).
  2. The sample tables TableCD/multiple tables calculation are in Procedure TestSP ( to run faster).
  3. In remote server table required to Insert the records, If records already exists in remote server table then update if any change happened in point no1.
  4. If any new rows available in point 1 (The sample table TableCd ( there are multiple tables like TableCD)
    then insert a records in remote server table from Procedure TestSP.
    Hope i make it clear.