如何将语句执行结果保存到表中 insert into #tab_dest select uid, name from sysusersselect * from #tab_dest 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 晕,楼主不是楼上说的那个意思吧?楼主是想把两个字段合并成一个字段,并且为text型?除了楼主说的改造语句的方法,我想不出别的方法了 insert into #tab_dest([c1],[text])select uid,name from sysusers 那这样可以不??Create Table #tab_dest(c1 text)Insert #tab_dest Select Rtrim(uid)+' '+name from sysusers Where uid>10Select * from #tab_destDrop Table #tab_dest/*c116385 db_accessadmin16389 db_backupoperator16390 db_datareader16391 db_datawriter16387 db_ddladmin16392 db_denydatareader16393 db_denydatawriter16384 db_owner16386 db_securityadmin*/ mengzulin(Julian),没看清题意。 谢谢楼上几位的帮忙,但不能达到我的需求.即便通过改造语句,也需要使用游标或循环将其插入目标表.注意目标表只保存一条数据,而不是结果集的行数.结合示例,#tab_dest的记录数为1,c1的值为'uid name16385 db_accessadmin16389 db_backupoperator16390 db_datareader16391 db_datawriter16387 db_ddladmin16392 db_denydatareader16393 db_denydatawriter16384 db_owner16386 db_securityadmin'.而不是通常理解的结果:#tab_dest的记录数为9,c1逐笔的值为第1笔:'16385 db_accessadmin'第2笔:'1638516389 db_backupoperator'第3笔:'1638516390 db_datareader'第4笔:'1638516391 db_datawriter'第5笔:'1638516387 db_ddladmin'第6笔:'1638516392 db_denydatareader'第7笔:'1638516393 db_denydatawriter'第8笔:'1638516384 db_owner'第9笔:'1638516386 db_securityadmin' Create Table #tab_dest(c1 text)Declare @S Varchar(8000)Set @S='uid name'Select @S=@S+Char(13)+Rtrim(uid)+' '+name from sysusers Where uid>10Insert #tab_dest Values(@S)Select @S=c1 from #tab_destPrint @SDrop Table #tab_dest/*(1 row(s) affected)uid name16385 db_accessadmin16389 db_backupoperator16390 db_datareader16391 db_datawriter16387 db_ddladmin16392 db_denydatareader16393 db_denydatawriter16384 db_owner16386 db_securityadmin*/ paoluo(一天到晚游泳的鱼) 提供的方法不跟我的差不多吗?用字符串联方法代替循环是聪明的写法,但有局限(不能突破8000个字节限制).用改造SELECT语句的方法需要先分析SELECT语句,如果SELECT语句较复杂,要正确分析比较困难.我使用了'语句'而不是'SELECT语句',即要把存储过程(有SELECT语句)的SELECT查询结果也保存起来,这时就不是分析SELECT能够解决的.还有要能正确处理动态语句的执行结果. create table #tab_dest(c1 text)insert into #tab_dest select 'uid name'declare @uid varchar(20),@name varchar(40),@s varchar(100),@len intDECLARE t_cursor CURSOR FOR select uid,name from sysusersOPEN t_cursorFETCH NEXT FROM t_cursor INTO @uid,@nameWHILE @@FETCH_STATUS = 0BEGIN select @len = datalength([c1]) from #tab_dest print @len 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,@nameENDCLOSE t_cursorDEALLOCATE t_cursorselect * from #tab_dest 以操作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_destDECLARE t_cursor CURSOR FOR SELECT uid,name FROM sysusersOPEN t_cursorFETCH NEXT FROM t_cursor INTO @uid,@nameWHILE @@FETCH_STATUS = 0BEGIN 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,@nameENDCLOSE t_cursorDEALLOCATE t_cursor--查看更新结果SELECT C1 FROM #tab_dest libin_ftsafe(子陌红尘) 的方法只能解决示例的结果保存,不能解决一类问题(虽然可能类比).如果使用一个中间表(动态创建)保存结果集每行数据,再循环形成最终结果(TEXT操作),即将每行数据串联成一条数据,这可以解决SELECT结果集的保存.但同样出现我讲的问题:用改造SELECT语句的方法需要先分析SELECT语句,如果SELECT语句较复杂,要正确分析比较困难.我使用了'语句'而不是'SELECT语句',即要把存储过程(有SELECT语句)的SELECT查询结果也保存起来,这时就不是分析SELECT能够解决的.还有要能正确处理动态语句的执行结果. 结果集字符数大于8000的情况很普遍,当然可以通过循环(全部行循环和分段循环)来突破8000限制.对于SELECT语句,分析的问题解决就好办些.动态语句和存储过程返回结果只有实际执行才能知道究竟返回哪些元素和数据(一个或多个结果),这就很难预知了. 以操作TEXT列的标准函数 UPDATETEXT 实现对TEXT列数据的追加:(修改了@len变量数据的获取方式)--------------------------------------------------------------------------------声明过程变量DECLARE @uid VARCHAR(20),@name VARCHAR(40),@s VARCHAR(100),@len INT--创建临时表,并将表头信息插入表中CREATE TABLE #tab_dest(c1 TEXT)INSERT INTO #tab_dest SELECT 'uid name'--获取c1数据的初始长度SELECT @len = DATALENGTH([c1]) FROM #tab_dest--以游标方式获取sysusers中数据,并以UPDATETEXT方式更新到临时表#tab_destDECLARE t_cursor CURSOR FOR SELECT uid,name FROM sysusersOPEN t_cursorFETCH NEXT FROM t_cursor INTO @uid,@nameWHILE @@FETCH_STATUS = 0BEGIN SET @s = CHAR(13)+@uid+' '+@name SET @len = @len + DATALENGTH(@s) 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,@nameENDCLOSE t_cursorDEALLOCATE t_cursor--查看更新结果SELECT C1 FROM #tab_dest 如何实现视图中添加标识列? 如何字段及数据都一起导出到test.xls中呢? sql查询语句2 sql2005导入数据库提示varchar 字转换 bit 发生错误 SQL 有没有累积函数的呢? SQL SERVER 2005 自定义函数<小写数字转大写> 我要导入数据到动网论坛的库里面,是不是要用触发器呢 简单问题 诚心求讲解数据库的底层的书。 如何将数据导出为CSV文档? 请教set fmtonly on是如何工作的 如何消除通过insert into openrowset插入到文本文件的双引号
除了楼主说的改造语句的方法,我想不出别的方法了
select uid,name from sysusers
Insert #tab_dest Select Rtrim(uid)+' '+name from sysusers Where uid>10
Select * from #tab_destDrop Table #tab_dest
/*
c1
16385 db_accessadmin
16389 db_backupoperator
16390 db_datareader
16391 db_datawriter
16387 db_ddladmin
16392 db_denydatareader
16393 db_denydatawriter
16384 db_owner
16386 db_securityadmin
*/
16385 db_accessadmin
16389 db_backupoperator
16390 db_datareader
16391 db_datawriter
16387 db_ddladmin
16392 db_denydatareader
16393 db_denydatawriter
16384 db_owner
16386 db_securityadmin'.
而不是通常理解的结果:#tab_dest的记录数为9,c1逐笔的值为
第1笔:'16385 db_accessadmin'
第2笔:'1638516389 db_backupoperator'
第3笔:'1638516390 db_datareader'
第4笔:'1638516391 db_datawriter'
第5笔:'1638516387 db_ddladmin'
第6笔:'1638516392 db_denydatareader'
第7笔:'1638516393 db_denydatawriter'
第8笔:'1638516384 db_owner'
第9笔:'1638516386 db_securityadmin'
Declare @S Varchar(8000)
Set @S='uid name'
Select @S=@S+Char(13)+Rtrim(uid)+' '+name from sysusers Where uid>10
Insert #tab_dest Values(@S)Select @S=c1 from #tab_dest
Print @SDrop Table #tab_dest
/*(1 row(s) affected)uid name
16385 db_accessadmin
16389 db_backupoperator
16390 db_datareader
16391 db_datawriter
16387 db_ddladmin
16392 db_denydatareader
16393 db_denydatawriter
16384 db_owner
16386 db_securityadmin
*/
insert into #tab_dest select 'uid name'
declare @uid varchar(20),@name varchar(40),@s varchar(100),@len intDECLARE 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
print @len
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_cursorselect * 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
(修改了@len变量数据的获取方式)
------------------------------------------------------------------------------
--声明过程变量
DECLARE @uid VARCHAR(20),@name VARCHAR(40),@s VARCHAR(100),@len INT--创建临时表,并将表头信息插入表中
CREATE TABLE #tab_dest(c1 TEXT)
INSERT INTO #tab_dest SELECT 'uid name'--获取c1数据的初始长度
SELECT @len = DATALENGTH([c1]) FROM #tab_dest--以游标方式获取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
SET @s = CHAR(13)+@uid+' '+@name
SET @len = @len + DATALENGTH(@s)
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