以操作TEXT列的标准函数 UPDATETEXT 实现对TEXT列数据的追加:
---------------------------------------------------------------------------------创建临时表,并将表头信息插入表中
CREATE TABLE #tab_dest(c1 TEXT)
INSERT INTO #tab_dest SELECT 'uid name'--声明过程变量
DECLARE @uid VARCHAR(20),@name VARCHAR(40),@s VARCHAR(100),@len INT--以游标方式获取sysusers中数据,并以UPDATETEXT方式更新到临时表#tab_dest
DECLARE t_cursor CURSOR FOR
SELECT uid,name FROM sysusersOPEN t_cursorFETCH NEXT FROM t_cursor INTO @uid,@nameWHILE @@FETCH_STATUS = 0
BEGIN
SELECT @len = DATALENGTH([c1]) FROM #tab_dest
SET @s = CHAR(13)+@uid+' '+@name
DECLARE @ptrval binary(16)
SELECT
@ptrval = TEXTPTR(c1)
FROM
#tab_dest
UPDATETEXT #tab_dest.[c1] @ptrval @len 0 @s
FETCH NEXT FROM t_cursor INTO @uid,@name
ENDCLOSE t_cursor
DEALLOCATE t_cursor--查看更新结果
SELECT C1 FROM #tab_dest
---------------------------------------------------------------------------------创建临时表,并将表头信息插入表中
CREATE TABLE #tab_dest(c1 TEXT)
INSERT INTO #tab_dest SELECT 'uid name'--声明过程变量
DECLARE @uid VARCHAR(20),@name VARCHAR(40),@s VARCHAR(100),@len INT--以游标方式获取sysusers中数据,并以UPDATETEXT方式更新到临时表#tab_dest
DECLARE t_cursor CURSOR FOR
SELECT uid,name FROM sysusersOPEN t_cursorFETCH NEXT FROM t_cursor INTO @uid,@nameWHILE @@FETCH_STATUS = 0
BEGIN
SELECT @len = DATALENGTH([c1]) FROM #tab_dest
SET @s = CHAR(13)+@uid+' '+@name
DECLARE @ptrval binary(16)
SELECT
@ptrval = TEXTPTR(c1)
FROM
#tab_dest
UPDATETEXT #tab_dest.[c1] @ptrval @len 0 @s
FETCH NEXT FROM t_cursor INTO @uid,@name
ENDCLOSE t_cursor
DEALLOCATE t_cursor--查看更新结果
SELECT C1 FROM #tab_dest
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货