1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| MERGE ICE_Product T
USING
(
SELECT DISTINCT
C.value('./@ID','int')as ProductID
,C.value('./@Code','integer') as Code
,C.value('./@HighPic','varchar(1000)') as HighPic
,C.value('./@HighPicHeight','int') as HighPicHeight
,C.value('./@HighPicSize','int') as HighPicSize
,C.value('./@HighPicWidth','int')as HighPicWidth
,C.value('./@LowPic','varchar(1000)') as LowPic
,C.value('./@LowPicHeight','int') as LowPicHeight
,C.value('./@LowPicSize','int') as LowPicSize
,C.value('./@LowPicWidth','int') as LowPicWidth
,C.value('./@Name','varchar(1000)') as Name
,C.value('./@Pic500x500','[varchar](1000)') as Pic500x500
,C.value('./@Pic500x500Height','int') as Pic500x500Height
,C.value('./@Pic500x500Size','int') as Pic500x500Size
,C.value('./@Pic500x500Width','int') as Pic500x500Width
,C.value('./@Prod_id','varchar(255)') as Prod_id
,C.value('./@Quality','varchar(255)') as Quality
,C.value('./@ReleaseDate','datetime') as ReleaseDate
,C.value('./@ThumbPic','varchar(1000)') as ThumbPic
,C.value('./@ThumbPicSize','int') as ThumbPicSize
,C.value('./@Title','varchar(2000)') as Title
,null
,GETDATE()
FROM T_Fiche_Icecat T
CROSS APPLY T.ContenuXML.nodes('ICECAT-interface/Product') AS T_XML(C)
WHERE ContenuXML.exist('ICECAT-interface/Product')=1
) S
ON T.ProductId = S.Id
WHEN NOT MATCHED THEN
INSERT ( ProductID, Code, HighPic, HighPicHeight, HighPicSize, HighPicWidth, LowPic, LowPicWidth, Name, Pic500x500, Pic500x500Height, Pic500x500Size, Pic500x500Width, Prod_id, Quality, ReleaseDate, ThumbPic, ThumbPicSize, Title)
VALUES (S.ProductID, S.Code, S.HighPic, S.HighPicHeight, S.HighPicSize, S.HighPicWidth, S.LowPic, S.LowPicWidth, S.Name, S.Pic500x500, S.Pic500x500Height, S.Pic500x500Size, S.Pic500x500Width, S.Prod_id, S.Quality, S.ReleaseDate, S.ThumbPic, S.ThumbPicSize, S.Title)
END; |
Partager