不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

请教高手, 表A,表B格式完全相同,只有个别字段的值不一样，如何写替换的SQL?

楼主ryan_www（点1）2006-02-20 10:15:21 在 MS-SQL Server / 疑难问题提问

表A,表B格式完全相同只有个别字段的值不一样, 现在需要用表B的这条记录替换掉表A的对应记录
所有字段都复制过去，能不能写一个类似update A set * (select * from B)的语句
可以实现替换，又不用在sql语句列举所有的字段。问题点数：100、回复次数：20Top

1 楼happyflystone（无枪的狙击手）回复于 2006-02-20 10:18:22 得分 0

不能吧，必须指明字段吧Top

2 楼ryan_www（点1）回复于 2006-02-20 10:22:08 得分 0

那字段太多了，几百个呢，有什么宏可以代替么，或者能自动生成如下的字段列表
"a.col1 = b.col1 a.col2 = b.col2 ......a.col100 = b.col100"...Top

3 楼ryan_www（点1）回复于 2006-02-20 10:25:58 得分 0

或者有什么sqlserver本身配备的功能，可以直接使用来实现这个功能:
表B是表A前段时间的备份, 现在要用表B里指定的一条记录恢复到表A里面对应的位置Top

4 楼zjcxc（邹建）回复于 2006-02-20 10:26:01 得分 0

update必须指明列名Top

5 楼happyflystone（无枪的狙击手）回复于 2006-02-20 10:26:57 得分 10

declare @s varchar(8000)
set @s = ''
declare @i int
set @i = 0
while @i<100
begin
set @s = ',' + 'a.col'+convert(varchar(3),@I)+' = 'b.col'+ convert(varchar(3),@I)
set @i = @I +1
end
set @s = stuff(@s,1,1,'')
print @s
Top

6 楼unsigned（僵哥(发站内消息，请附上链接或问题说明，否则不予回复)）回复于 2006-02-20 10:28:28 得分 5

表A
field0 field1 field2
表B
field1 field2 field3

select [field0],[field1],[field2] from [表A]

replace(replace('select [field0],[field1],[field2] from [表A]','[field0]','[field3]'),'[表A]','[表B]')Top

7 楼zjcxc（邹建）回复于 2006-02-20 10:29:00 得分 85

当然, 可以通过读系统表自动生成update语句

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(name)+'=b.'+quotename(name)
from syscolumns
where id=object_id('表A')
set @s=stuff(@s,1,1,'')
exec('update a set '+@s+' from 表A a, 表B b where 条件')Top

8 楼ryan_www（点1）回复于 2006-02-20 10:32:03 得分 0

谢谢楼上的脚本，不过...
col1, col2 只是代号，实际上都是有特别的字段名:
表A, 表B
cha_id cha_name icon_id score level .....100来个...., cha_id是主键, cha_name不可重复
Top

9 楼zjcxc（邹建）回复于 2006-02-20 10:54:27 得分 0

我的脚本是直接从系统表获取列名, 所以你的表是什么列, 则会自动取出什么列名, 不会限制于col1之类Top

10 楼ryan_www（点1）回复于 2006-02-20 11:02:47 得分 0

嗯，我正在测试zjcxc提供的脚本，再请教一下，如果
表A数据库名是"DB1", 表B的数据库名是"DB2", 那SQL语法上如何体现呢，能实现么?Top

11 楼zjcxc（邹建）回复于 2006-02-20 11:05:51 得分 0

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(c.name)+'=b.'+quotename(c.name)
from DB1.syscolumns c, DB2.sysobjects o
where c.id=o.id and c.name = '表A'
set @s=stuff(@s,1,1,'')
exec('update a set '+@s+' from db1..表A a, db2..表B b where 条件')
Top

12 楼ryan_www（点1）回复于 2006-02-20 11:11:55 得分 0

执行sql的时候，有些语法错误,
下面这条语句
select @s=@s+','+quotename(c.name)+'=b.'+quotename(c.name)
'=b.', b就是表B的名字么, 但是会有语法错误，比如表B的名字是'abc'
Top

13 楼ryan_www（点1）回复于 2006-02-20 11:15:57 得分 0

表A的名字是character, 表B的名字是abc, 两张表都在同一个DB, 执行如下的脚本
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(name)+'=abc.'+quotename(name)
from syscolumns
where id=object_id('character')
set @s=stuff(@s,1,1,'')
print @s
exec('update character set '+@s+' where cha_id = 1')
----------------------------------------------------
输出
服务器: 消息 107，级别 16，状态 3，行 1
列前缀 'abc' 与查询中所用的表名或别名不匹配。Top

14 楼zjcxc（邹建）回复于 2006-02-20 12:13:25 得分 0

-- 楼主没有做过两个表连接的update ???. 显然是楼主的语法搞错了

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(name)+'=abc.'+quotename(name)
from syscolumns
where id=object_id('character')
set @s=stuff(@s,1,1,'')
print @s
exec('update a set '+@s+' from character a, abc b where cha_id = 1') -- 别名及from
Top

15 楼ryan_www（点1）回复于 2006-02-20 13:58:20 得分 0

嗯，谢谢，搞定了，但现在还有一个特别的需求
因为表中 cha_id 字段和cha_name不让修改'唯一标识'
怎样在自动成生字段的脚本中就把这两个字段给屏蔽掉呢Top

16 楼zjcxc（邹建）回复于 2006-02-20 14:01:52 得分 0

-- 加过滤条件嘛

declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(name)+'=abc.'+quotename(name)
from syscolumns
where id=object_id('character')
and name not in('cha_id', 'cha_name')
set @s=stuff(@s,1,1,'')
print @s
exec('update a set '+@s+' from character a, abc b where cha_id = 1') -- 别名及fromTop

17 楼happyflystone（无枪的狙击手）回复于 2006-02-20 14:04:21 得分 0

SET IDENTITY_INSERT
允许将显式值插入表的标识列中。

语法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

参数
database

是指定的表所驻留的数据库名称。

owner

是表所有者的名称。

table

是含有标识列的表名。

注释
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft® SQL Server™ 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。

如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。

Top

18 楼ryan_www（点1）回复于 2006-02-20 14:16:10 得分 0

哈哈，搞定！，马上给分，再来最后一个完美性的问题，怎么把这个过程封装成一个函数来调用
参数是两个表名和条件字符串, 类似于
function REPLACE_REC（tab1, tab2, condition_str)
.....
end

REPLACE_REC('character', 'abc', 'cha_id = 1')

ps : 回佛来德, SET IDENTITY_INSERT 好像不起作用Top

19 楼zjcxc（邹建）回复于 2006-02-20 15:01:20 得分 0

set identity_insert 是针对插入时, 强制插入标识列的, 对于update当然无效.Top

20 楼zjcxc（邹建）回复于 2006-02-20 15:04:15 得分 0

函数只能生成sql语句, 不能执行. 所以可以写到生成sql语句这一层

create function f_sql(
@stb sysname,
@dtb sysname,
@where nvarchar(1000)
) returns varchar(8000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+quotename(name)+'=abc.'+quotename(name)
from syscolumns
where id=object_id(@stb)
and status & 0x80 = 0
-- and name not in('cha_id', 'cha_name')
set @s=stuff(@s,1,1,'')
set @s = 'update a set '+@s+' from '+@stb+' a, '+@dtb+' b where '+@where
return(@s)
end
Top