The trigger as written will only be able to work with one row at a time.

there would be a lot of data lost if a large set of data were to be inserted in one operation.

As I advised in my previous post, read up about using the "inserted" virtual table and change your code to cater for using data sets.

I have created a mock-up situation, using your given details (not all the columns, just some critical ones...

CREATE TABLE dbo.theResult (

theResultNo INT IDENTITY (1,1) PRIMARY KEY,

Message NVARCHAR(56) NOT NULL)

Go

CREATE TABLE dbo.OutputDetail (

theResultNo INT FOREIGN KEY REFERENCES dbo.theResult(theResultNo),

Message NVARCHAR(56) NOT NULL,

north nvarchar(30),

east nvarchar(30),

mtime nvarchar(30),

height nvarchar(30),

temperature nvarchar(30),

voltage nvarchar(30),

luman nvarchar(30),

comprasser nvarchar(30))

GO

CREATE TRIGGER CaptureOutput

ON dbo.theResult

FOR INSERT

AS

BEGIN

BEGIN TRY

WITH Characters (CharindexValues) AS

(SELECT * FROM (VALUES ('N'), ('E'), ('M'), ('H'), ('T'), ('V'), ('L'), ('C'), ('XX')) CiV(Val)),

Points AS

(SELECT theResultNo, CharindexValues, CHARINDEX(CharindexValues, Message+'XX') Point, ROW_NUMBER() OVER (ORDER BY CHARINDEX(CharindexValues, Message+'XX')) [rows]

FROM Characters

CROSS JOIN inserted),

StringOut AS

(select a.theResultNo, a.CharindexValues, a.Point, a.rows, CASE WHEN a.Point > 0 THEN SUBSTRING(Message, CASE WHEN b.Point IS NOT NULL THEN b.Point ELSE 0 END+1, CASE WHEN a.Point IS NOT NULL THEN a.Point ELSE 1 END - CASE WHEN b.Point IS NOT NULL THEN b.Point+1 ELSE 1 END) ELSE NULL END AS StringOut

from inserted

JOIN Points a on a.theResultNo = inserted.theResultNo

left join Points b on b.rows = a.rows - 1),

PivotOut AS

(select theResultNo, [1] AS North, [2] AS East, [3] AS mtime, [4] AS Height, [5] AS Temp, [6] AS Voltage, [7] AS Luman, [8] AS Comprasser

From (SELECT theResultNo, rows, StringOut

FROM StringOut

WHERE LEN(StringOut)>0) s

pivot(max(StringOut)

FOR rows in ([1],[2],[3],[4],[5],[6],[7],[8])) pvt

group by theResultNo,[1],[2],[3],[4],[5],[6],[7],[8])

INSERT dbo.OutputDetail

SELECT inserted.theResultNo, CASE WHEN CHARINDEX('N',Message) > 0 THEN Message ELSE 'Status not sent by OZEKI!Request again' END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN North ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN East ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN mtime ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN Height ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN Temp ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN Voltage ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN Luman ELSE NULL END,

CASE WHEN CHARINDEX('N',Message) > 0 THEN Comprasser ELSE NULL END

FROM inserted

JOIN PivotOut ON PivotOut.theResultNo = inserted.theResultNo

--select * from Points

END TRY

BEGIN CATCH

DECLARE @ErrLine INT = ERROR_LINE(), @ErrNum INT = ERROR_NUMBER(), @ErrMsg NVARCHAR(1024) = ERROR_MESSAGE(), @ErrSev INT = ERROR_SEVERITY(), @ErrState INT = ERROR_STATE()

RAISERROR('Something went wrong at line %d, err number %d, with message %s, severity %d, State %d',16,1, @ErrLine, @ErrNum, @ErrMsg, @ErrSev, @ErrState)

ROLLBACK

END CATCH

--COMMIT

END

GO

INSERT theResult (Message)

VALUES ('2449.555N06704.2855EM0701H071T44.098V11.764L0.372C1')

INSERT theResult (Message)

VALUES ('2459.545N06514.2455E1M0731H072T45.198V10.754L0.362C1')

INSERT theResult (Message)

VALUES ('100155.55E2455E1M0731H072T45')

GO

SELECT * FROM dbo.theResult

SELECT * FROM dbo.OutputDetail

GO

DROP TABLE dbo.OutputDetail

DROP TABLE dbo.theResult