newtb 是一个已存在的表,有一个aid和那个表相匹配的。现在是增加一列记录数据,不要那个旧表了。
解决方案 »
- sql2000保存byte[]问题
- 从一张表中拆分数据到n张表
- 急呀,访问MICROSOFT.JET.OLEDB.4.0程序出错!!!
- @m m LEFT OUTER JOIN @mm ON m.EnterpriseEmployeeID = @mm.EnterpriseEmployeeID -----必须声明变量 '@mm'。
- 使用触发器同时插入,能否根据某一字段判断是否点火数据库。
- 建立触发器问题
- 安装sql server的问题
- sqlserver中这样才能得到datetime的日期而不要时间??
- Sql server 表格约束的问题,请高手解答
- **更改服务器IP后,通过ADO访问SQL2000的客户端程序提示无法连接服务器**!!WHY??
- SqlServer2000查询数据表的优化问题
- 插入、修改数据时,数据库是必须写入磁盘才返回成功,还是延迟写入,先返回成功!
if object_id('test') is not null drop table testcreate table test(id int,aid int,itemid int)insert into test select 2,3,111
union all
select 4,3,222
union all
select 6,4,333
union all
select 7,3,666
declare @t1 table(aid int,content nvarchar(100))
declare @aid int
declare test_add cursor for select aid from test group by aid
open test_add
fetch next from test_add into @aid
while @@fetch_status=0
begin
declare @t nvarchar(100)
select @t=isnull(@t,'')+','+cast(itemid as varchar(30)) from test where aid=@aid
insert @t1 select @aid,stuff(@t,1,1,'')
set @t=''
fetch next from test_add into @aid
end
close test_add
deallocate test_add
select * from @t1
select
aid,itemIds=stuff((select ','+ltrim([itemId]) from oldtb where aid=t.aid for xml path('')), 1, 1, '')
from oldtb t
group by aid
create table oldtb
(id int,aid int,itemid int)insert into oldtb
select 2,3,111 union all
select 4,3,222 union all
select 6,4,333 union all
select 7,3,666create table newtb
(aid int,itemIds varchar(20))
insert into newtb(aid,itemIds)
select a.aid,
stuff((select ','+rtrim(b.itemid)
from oldtb b
where b.aid=a.aid
for xml path('')),1,1,'')
from oldtb a
group by a.aid
select * from newtb/*
aid itemIds
----------- --------------------
3 111,222,666
4 333(2 row(s) affected)
*/