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