在sql server 2005中有一个数据库,其中一个表A内有很多条数据,现需要修改一部分,但这部分没有一定的规则,所以导出到Excel中来修改,修改后,在Excel中将没有修改的记录全部删除了,然后将这个Excel文件导入到这个数据库中一个新表B,请问我想将B表的数据导入到A表内,导入后A表内的部分数据值为B表的数据值,且不重复。谢谢。
update table_a set userid = u.code, username = u.name, userage = u.age from table_a a,user_table u where u.id=a.id and (a.userid <> u.code or a.username <> u.name or a.userage <> u.age)
user_table id code name age 1 1001 Jack 30 2 1002 Colin 31 3 1003 Jason 24 4 1004 Wicher 33table_a id userid username userage 1 1001 Jack 30 2 1002 Colin 31 3 1003 Jason 24 4 1006 Wicher 33执行上面的SQL(1 行受影响)
提供A表的主键么,根据A表B表主键关联做update
谢谢上面各位的回复。原来这样就可以了update Customers set Customers.Address=Customers1.Address FROM Customers INNER join Customers1 on Customers.CustomerID=Customers1.CustomerID
set userid = u.code,
username = u.name,
userage = u.age
from table_a a,user_table u
where u.id=a.id
and (a.userid <> u.code
or a.username <> u.name
or a.userage <> u.age)
id code name age
1 1001 Jack 30
2 1002 Colin 31
3 1003 Jason 24
4 1004 Wicher 33table_a
id userid username userage
1 1001 Jack 30
2 1002 Colin 31
3 1003 Jason 24
4 1006 Wicher 33执行上面的SQL(1 行受影响)
set Customers.Address=Customers1.Address
FROM Customers INNER join Customers1
on Customers.CustomerID=Customers1.CustomerID
把A表删了,
用Excel导成A表
根据主键将a表中与B表主键相同的行删除,
再将B表插入A表
set Customers.Address=Customers1.Address
FROM Customers INNER join Customers1
on Customers.CustomerID=Customers1.CustomerID