1 Reply Latest reply on Oct 24, 2018 2:29 PM by csawyer

    How to insert value by PI-OLEDB from Primary to Other PI

    Techapat

      I'm using linked server in SQL Server for insert but i don't know why i can't insert data.

      This code below.

      Code :

      -- VARIABLES

      DECLARE @TSQL varchar(200)

      DECLARE @VAL float

      DECLARE @STRINGVAL varchar(20)

      DECLARE @STRINGDATEBEGIN varchar(20)

      DECLARE @STRINGDATEEND varchar(20)

      -- DEFINE PERIOD

      SET @STRINGDATEBEGIN = CONVERT (VARCHAR(50), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),20) --First day of previous month

      SET @STRINGDATEEND = CONVERT (VARCHAR(50), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),20) --First day of current month

      -- GET ARCHIVE VALUE FROM PRIMARY

      SELECT @TSQL = '(SELECT tag,time,value FROM OPENQUERY([PRIMARYPI], ''SELECT tag,time,value FROM piarchive.picomp2

      WHERE tag = ''''sinusoid'''' AND time BETWEEN ''''' + @STRINGDATEBEGIN + ''''' AND ''''' + @STRINGDATEEND + '''''''))'

      -- DEFINE TABLE

      DECLARE @t TABLE (Tag varchar(100),Times varchar(100),VAL float)

      INSERT @t

      EXEC(@TSQL)

      -- INSERT OTHER PI

      SELECT @TSQL = 'SELECT * FROM OPENQUERY([OTHERPI], ''INSERT piarchive..picomp2 SELECT Tag,CAST(Times as datetime2),VAL FROM @t'')'

      EXEC(@TSQL)

       

      But i think function insert other pi error but i don't know how can i fix it.