I am new to SQL Server and my task is to extract thousands of records from C# as an XML file, then import the data into a temp table. After that, I need to check each record one by one and update it if it exists, otherwise insert it. To achieve this, I have written a stored procedure. However, I encountered the following error:
Msg 102, Level 15, State 1, Procedure InsertIntoMyTable, Line 13
Incorrect syntax near 'cast'.
Msg 102, Level 15, State 1, Procedure InsertIntoMyTable, Line 18
Incorrect syntax near 'LOOP'.
Msg 156, Level 15, State 1, Procedure InsertIntoMyTable, Line 25
Incorrect syntax near the keyword 'END'.
Here is the stored procedure:
Here is the stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertIntoMyTable
@mytable xml
AS
BEGIN
SELECT
cast(colx.query('data(PointsliceId) ') as int) as PointSliceId,
cast(colx.query('data(Pt_timestamp) ') as datetime) as Point_timestamp
cast(colx.query('data(FloatValue) ') as float) as Float_Value
INTo #TMP FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)
For IDX in (select * from TMP)
LOOP
if((select count(*) from PointValue_Float where PointSliceId=IDX.PointSliceId and Pt_timeStamp=IDX.Pt_timeStamp)>0 )
update PointValue_Float set FloatValue=t.FloatValue from #TMP t where t.PointSliceId=PointValue_Float.PointSliceId and t.Pt_timeStamp=PointValue_Float.Pt_timeStamp
else
insert into PointValue_Float(PointSliceId,Pt_timeStamp,FloatValue) SELECT PointSliceId,Pt_timeStamp,FloatValue FROM #TMP
END LOOP
END
GO
- 我的表名是pointvalue_float,我需要检查数据是否存在,如果存在则更新,否则插入。
scan...endscan
那些美好的日子啊!有时候我真希望它们永远不会过去。 - Andriy M