求一批量转移数据的SQL脚本

[wildfire555]

最近在转换客户的一个购物商城，他原来有一个网站，但产品数据表里面的产品图片数据不太合理，原来的开发者是把所有图片都一起堆放在一个字段里面的。如下（其他字段我省略了）：

ProductId ProductImage
717 9061-2 front.jpg 9061-2 back.jpg 9061-2 bottom.jpg
716 9061-1 front.jpg 9061-1 back.jpg 9061-1 bottom.jpg
715 8399.jpg 8399 back.jpg 8399 bottom.jpg 8399 side.j
812 868378J front.jpg 868378J back.jpg 868378J bottom.
811 868377 front.jpg 868377.jpg
810 868369-J-2 front.jpg 868369-J-2.jpg
809 868369-J-1 front.jpg 868369-J-1.jpg
808 868368S front.jpg 868368S back.jpg 868368S bottom.
807 868368L front.jpg 868368L back.jpg 868368L bottom.
806 868358-A front.jpg 868358-A.jpg
805 8496 front.jpg 8496.jpg

现在我想把这个表的图片数据到处到另外一个表ProductsPictures，作为产品表和产品图片的关联表。我希望导入之后的数据表ProductsPictures中的数据是这个样子的：

ProductId ProductUrl
717 9061-2 front.jpg
717 9061-2 back.jpg
717 9061-2 bottom.jpg
716 9061-1 front.jpg
716 9061-1 back.jpg
716 9061-1 bottom.jpg

望高手赐教。

[--小F--]

拆分字符串

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';

--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
    select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
    union all
    select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';

--> 2. 临时表法：速度比CTE方法相差无几
if object_id('tempdb.dbo.#') is not null drop table #
select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=','
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/

3. XML法：速度较慢
select
    a.id,b.name
from
    (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
    (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/

[--小F--]

分拆列值 

原著：邹建 
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳 

有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 

1. 旧的解决方法(sql server 2000) 
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' 

DROP TABLE # 

2. 新的解决方法(sql server 2005) 

create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT A.id, B.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)A 
OUTER APPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) 
)B 

DROP TABLE tb 

/* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 

(5 行受影响) 
*/

[guguda2008]

小F那个一改就行了，我就不写了

[lihan6415151528]

http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx

create table tb (a varchar(10),b varchar(10))
insert tb
select '2009', 'AA' union all 
select '2009', 'BB' union all 
select '2008', 'CC' union all 
select '2007', 'XX' union all 
select '2009', 'HH' union all 
select '2008', 'DD' union all 
select '2007', 'SS' union all 
select '2006', 'GG' 
go
select * from tb


CREATE FUNCTION dbo.f_str(@col1 varchar(10)) 
RETURNS varchar(100) 
AS 
BEGIN 
    DECLARE @re varchar(100) 
    SET @re='' 
    SELECT @re=@re+','+CAST(b as varchar) 
    FROM tb 
    WHERE a=@col1 
    RETURN(STUFF(@re,1,1,'')) 
END 
GO

select a,dbo.f_str(a)[b] from tb group by a 

a          b                                   
---------- -------------------
2006       GG
2007       XX,SS
2008       CC,DD
2009       AA,BB,HH

（所影响的行数为 4 行）

[wildfire555]

注意产品数据表中图片字段的分隔符是.jpg，而且在尾部也有.jpg

[黄_瓜]

xiaoF给的改改就可以的

[wildfire555]

有没有现成直接可用到我这个情况上的代码，给全分。太忙，没时间分析了。

[华夏小卒]

If not object_id('[tb]') is null 
    Drop table [tb] 
Go 
Create table [tb](ProductId int,ProductImage  varchar(150)) 
Insert [tb] Select 
717, '9061-2 front.jpg 9061-2 back.jpg 9061-2 bottom.jpg'  union all Select 
716, '9061-1 front.jpg 9061-1 back.jpg 9061-1 bottom.jpg' union all Select 
715, '8399.jpg 8399 back.jpg 8399 bottom.jpg 8399 side.jpg' 


--1、直接查询(这个看着重复的代码很多)

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT A.ProductId, 
SUBSTRING(left(replace(ProductImage,'.jpg','.jpg,'),len(replace(ProductImage,'.jpg','.jpg,'))-1), 
B.id, 
CHARINDEX(',', left(replace(ProductImage,'.jpg','.jpg,'),len(replace(ProductImage,'.jpg','.jpg,'))-1) + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + left(replace(ProductImage,'.jpg','.jpg,'),len(replace(ProductImage,'.jpg','.jpg,'))-1), B.id, 1) = ',' 

DROP TABLE # 

/*
ProductId   
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
717         9061-2 front.jpg
717          9061-2 back.jpg
717          9061-2 bottom.jpg
716         9061-1 front.jpg
716          9061-1 back.jpg
716          9061-1 bottom.jpg
715         8399.jpg
715          8399 back.jpg
715          8399 bottom.jpg
715          8399 side.jpg

(10 行受影响)
*/


--2、先把图片路径字段更新一下(这样效率要高点)

update tb 
set ProductImage=left(replace(ProductImage,'.jpg','.jpg,'),len(replace(ProductImage,'.jpg','.jpg,'))-1)
where charindex('.jpg',ProductImage)>0


SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 

SELECT A.ProductId, SUBSTRING(ProductImage, B.id, CHARINDEX(',', ProductImage + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + ProductImage, B.id, 1) = ',' 

DROP TABLE # 


ProductId   
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------
717         9061-2 front.jpg
717          9061-2 back.jpg
717          9061-2 bottom.jpg
716         9061-1 front.jpg
716          9061-1 back.jpg
716          9061-1 bottom.jpg
715         8399.jpg
715          8399 back.jpg
715          8399 bottom.jpg
715          8399 side.jpg

(10 行受影响)