DECLARE @SOInfoList TABLE
(
SONumber INT,
SODate datetime,
Status char(1)
)
INSERT INTO @SOInfoList
(
SONumber,
SODate,
Status
)
SELECT T.c.value(‘(SONumber/text())[1]‘, ‘int‘) AS SONumber
,T.c.value(‘(SODate/text())[1]‘, ‘nvarchar(50)‘) AS SODate
,T.c.value(‘(Status1/text())[1]‘, ‘char(1)‘) AS Status
FROM
@RealTimeSOList.nodes(‘/ArrayOfLoadDataMasterBuffer/LoadDataMasterBuffer‘) T (c)

UPDATE S
SET SODate = T.SODate,
Status1 = T.Status,
LastEditUser =‘SO‘,
LastEditDate = GETDATE()
FROM newegg.dbo.NESO_RealTimeSO_ProcessLog S
INNER JOIN @SOInfoList T
ON S.SONumber = T.SONumber

INSERT INTO newegg.dbo.NESO_RealTimeSO_ProcessLog
(
SONumber,
Sodate,
Status1,
InUser,
LastEditUser,
LastEditDate
)
SELECT SONumber,SODate,[Status],‘SO‘,‘SO‘,GETDATE() from @SOInfoList
WHERE SONumber NOT IN(SELECT SONumber FROM newegg.dbo.NESO_RealTimeSO_ProcessLog WITH(NOLOCK))

SQL中的循环,布布扣,bubuko.com

SQL中的循环