CREATE TABLE A(pkid int identity(1,1) primary key, myclass nvarchar(16), data image)
INSERT A
select 'A', 0x111111 union all
select 'A', 0x222222 union all
select 'A', 0x333333 union all
select 'A', 0x444444 union all
select 'B', 0x111111 union all
select 'B', 0x222222
GO
-- 处理
SELECT pkid, flag = 0, myclass, data
INTO #
FROM A
DECLARE tb CURSOR LOCAL FOR SELECT pkid, myclass, TEXTPTR(data) FROM # ORDER BY myclass, pkid
OPEN tb FETCH tb INTO @pkid, @myclass, @p2
WHILE @@FETCH_STATUS = 0
BEGIN
IF @myclass1 = @myclass
BEGIN
UPDATETEXT #.data @p1 NULL 0 @SP
UPDATETEXT #.data @p1 NULL 0 #.data @p2
END
ELSE
BEGIN
UPDATE # SET flag = 1, @p1 = @p2, @myclass1 = @myclass
WHERE pkid = @pkid
END
FETCH tb INTO @pkid, @myclass, @p2
END
CLOSE tb
DEALLOCATE tb
SELECT id=(SELECT COUNT(1) FROM # WHERE flag = 1 AND myclass<a.myclass) +1,myclass, data
FROM # a
WHERE flag = 1
GO
DROP TABLE #, A
--结果:
/*
id myclass data
-------------------------------------------------
1 A 0x111111222222333333444444
2 B 0x111111222222
*/
我举个例子来说明下我的意图:
--Table tbsrc,
create table tbsrc(id int identity(1,1) primary key,myclass nvarchar(16),data image);
id myclass data
---- ------ --------
1 A 0x111111
2 A 0x222222
3 A 0x333333
4 A 0x444444
5 B 0x111111
6 B 0x222222
只举例,实际上要处理的不只6条记录,2个类。
create table tbdest(id int identity(1,1) primary key,myclass nvarchar(16),data image);
目标:
合并到Talbe tbdest的结果:
id myclass data
---- ------ --------
1 A 0x111111222222333333444444
2 B 0x111111222222
游标的用法如下:
create table test_table1(id int identity(1,1) primary key,data image)
go
create table test_table2(id int identity(1,1) primary key,data image)
go
declare @curorsImage image
declare @AllIamge image
declare cursor_Field cursor for
select data
where data is not null
open cursor_Field
--如果何必我不懂,只告诉你游标怎么使用
fetch next from cursor_Field into @curorsImage
while @@fetch_status=0
begin
fetch next from cursor_Field into @curorsImage
end
close cursor_Field
deallocate cursor_Field
我给个简单的例子,多记录合并的话还需LZ自己写存储过程
create table test_table1(id int identity(1,1) primary key,data image)
create table test_table2(id int identity(1,1) primary key,data image)
insert into test_table1 select 0x111111 union all select 0x222222
select * from test_table1
DECLARE @target binary(16),@from binary(16)
insert into test_table2 select data from test_table1 where id=1
select @from=TEXTPTR(data) from test_table1 where id=2
select @target=TEXTPTR(data) from test_table2 where id=1
UPDATETEXT test_table2.data @target null 0 test_table1.data @from
select * from test_table2
drop table test_table1
drop table test_table2
(所影响的行数为 2 行)
id data
---- ---------------
1 0x111111
2 0x222222