USE MadeInChina
DUMP TRANSACTION MadeInChina WITH NO_LOG --清除日志
BACKUP LOG MadeInChina WITH NO_LOG
BACKUP LOG MadeInChina WITH TRUNCATE_ONLY
USE SeekPart_GatherAlibaba
--Test表是机械所有的分类
--筛选机械所有的产品复制表数据到一张新表
SELECT a.ID INTO dbo.ProductID FROM dbo.Product_Info A,Product_Class B WHERE A.CLASSNAME =B.ClassName
--修改产品表将图片表的数据以分号形式隔开
--拼接字符串,主要是用到了STUFF函数,将多行数据用字符串拼接起来,多用于一对多的表之间
UPDATE Product_Info SET ImgUrls= STUFF((SELECT ‘|http://img.weiku.com/‘+ImageUrl FROM ProductImage AS b WHERE ProductId=a1.ID FOR XML PATH(‘‘)) , 1 , 1 , ‘‘ )
FROM Product_Info a1
--,ProductImage a2 WHERE a1.ID=a2.ProductId (注释的这部分可以不要)
select top 100, ID , SELECT VV= stuff((select ‘,‘+ltrim(ID) from Keywords_ProductsTemp where MemberID=Keywords_MemberInfo.ID for xml path(‘‘)),1,1,‘‘) FROM Keywords_MemberInfo
--删除非机械所有数据(适用于百万级、千万级数据删除)
set rowcount 10000
WHILE 1 = 1
BEGIN
DELETE TOP(10000) FROM dbo.Product_Info WHERE ID NOT IN (SELECT ID FROM dbo.ProductID );
IF @@rowcount < 10000 BREAK;
END
set rowcount 0