数据迁移的sql语句解决方案

数据迁移的sql语句
旧版的程序设计没有独立的附件表,所有附件是用分号隔开合并存储在表中的某一个字段中的,但现在新的程序已经把附件存储到了另外一个新建的附件表中了,怎样把旧版的数据弄到新版的表中呢?

详情如下:
原来只有一张表如下

CREATE TABLE [dbo].[news](
[newsid] [int] NULL,
[attachmentFiles] [varchar](500) NULL,
[newtitle] [varchar](50) NULL
) ON [PRIMARY]

现在新增一张表如下:

CREATE TABLE [dbo].[newsAttachment](
[newsid] [int] NULL,
[filename] [varchar](50) NULL
) ON [PRIMARY]

原来的附件数据都是存在news.attachmentFiles字段中的,多个附件用分号隔开的,现在要把这些附件迁移到新表newsAttachment中,sql语句怎么写?

另外,这样的迁移前后,数据库的操作效率有区别吗?迁移主要是为了扩展,因为附件还有别的来源,还有别的属性等,统一放在一个字段中实在不方便.
[解决办法]
不知道是不是我理解问题，不就是把数据从一张表复制到另一张表吗？需要很大工程吗？
需要修改的只是程序读取附加的地方吧
2005以上可以类似这样操作

select * into #t from(
select 'x' a,'1,2'b
union all
select 'y','1'
union all
select 'z','1,2,3'
)a


select * from #t

select a.Col1,b.Col2  
from (
select a as Col1,convert(xml,'<row><b>'+rtrim(replace(b,',','</b><b>'))+'</b></row>') as Col2 from #t)a
outer apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/row/b')C(v))b 

/*
a    b
---- -----
x    1,2
y    1
z    1,2,3

(3 行受影响)

Col1 Col2
---- -------
x    1
x    2
y    1
z    1
z    2
z    3

(6 行受影响)
*/

至于效率就得看你扩展功能后，需要添加什么属性，需要如何查询，视情况来添加相应的索引
[解决办法]
是这样吗：

declare @tb table(a varchar(20),b varchar(30))


insert into @tb
select 'x' a,'1,2' b
union all
select 'y','1'
union all
select 'z','1,2,3'



select a,
       --b,
       SUBSTRING(t.b, number ,CHARINDEX(',',t.b+',',number)-number) as split
from @tb t,master..spt_values s
where s.number >=1
      and s.type = 'P'
      and SUBSTRING(','+t.b,s.number,1) = ','
/*
a   split
x1
x2
y1
z1
z2
z3
*/

[解决办法]

CREATE TABLE [dbo].[news](
    [newsid] [int] NULL,
    [attachmentFiles] [varchar](500) NULL,
    [newtitle] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[newsAttachment](
    [newsid] [int] NULL,
    [filename] [varchar](50) NULL
) ON [PRIMARY]

insert into [news]
 select 10,'10A;10B','n10' union all
 select 11,'11A;11T','n11' union all
 select 12,'12C','n12' union all
 select 13,'13D;13E','n13' union all
 select 14,'14X;14Y;14Z','n14'


-- 数据迁移
insert into newsAttachment(newsid,filename)
select a.newsid,
       substring(a.attachmentFiles,b.number,charindex(';',a.attachmentFiles+';',b.number)-b.number)
from news a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.attachmentFiles)
and substring(';'+a.attachmentFiles,b.number,1)=';'
   

select * from newsAttachment

/*
newsid      filename
----------- ---------------
10          10A
10          10B
11          11A
11          11T
12          12C
13          13D
13          13E
14          14X
14          14Y
14          14Z

(10 row(s) affected)
*/

查看更多 下一篇