how to use for loop in stored procedure sql server 2005
i am new in SQL server. my requirement is I have to take thousands of records from c# as a XML file and from that file I am taking data in temp table. And from table I have to check record one by one if it is present then Update else insert. So I have written a stored procedure, but it is giving me 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'.
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 whe开发者_StackOverflowre 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
- My Table Name Is pointvalue_float where i have to check data is present or not if present then update else insert
Creating a manual loop in a SQL Server procedure is always a bad idea - SQL Server operates in sets of data - and your statement should also be set-oriented.
In your case, what I would do is this:
- shred the XML into a temporary table (as you already do)
- then update the existing values based on a join condition
- remove those rows updated from the temporary table
- the remaining rows need to be inserted
So your code would be something like:
CREATE PROCEDURE InsertIntoMyTable @mytable xml
AS BEGIN
SELECT
colx.value('(PointsliceId)[1]', 'INT') AS PointSliceId,
colx.value('(Pt_timestamp)[1]', 'DATETIME') AS Point_timestamp
colx.value('(FloatValue)[1]', 'FLAOT') AS Float_Value
INTO #TMP
FROM @mytable.nodes('DocumentElement/mytable') AS Tabx(Colx)
-- udpate the existing rows
UPDATE dbo.PointValue_Float
SET FloatValue = t.FloatValue
FROM #TMP t
WHERE t.PointSliceId = PointValue_Float.PointSliceId
AND t.Pt_timeStamp = PointValue_Float.Pt_timeStamp
-- remove those from the #TMP table
DELETE FROM #TMP
WHERE EXISTS
(SELECT * FROM dbo.PointValue_Float
WHERE PointSliceId = #TMP.PointSliceId AND Pt_timeStamp = #TMP.Pt_timeStamp)
-- INSERT the remaining rows
INSERT INTO
dbo.PointValue_Float(PointSliceId, Pt_timeStamp, FloatValue)
SELECT
PointSliceId, Pt_timeStamp, FloatValue
FROM #TMP
END
- You are missing a comma on second line (case statement) at the end. The select fields must always be seperated by a comma. In your case, the second case statement, the comma at end of line is missing.
- You have to use BEGIN and END for starting and ending WHILE loop. WHILE is better to be used in stored procedures instead of FOR. Not sure if there is something like FOR in sql server stored procedures.
精彩评论