临时表：table1
正式表：table2
table1，table2表结构相同，有以下几列：
{
a1 varchar(50),主键
a2 varchar(50),主键
a3 varchar(50),主键
a4 bigint(8),
a5 varchar(50)
}
以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
问题：把数据从table1 导入到table2中，
要求：如果table1中的a1,a2,a3三列和table2中的a1,a2,a3三列不全相同，做Insert操作，否则：Update a4,a5二列值。
现在我做转档的时候，提示在table2中不能插入重复主键？？？不知道什么原因？？？？？
急！！！！！！
52 个解决方案
#1
應該用遊標吧.
#2
按你的要求，貌似不能直接导数据吧，
可以用存储过程，游标历遍全表进行插入和更新
可以用存储过程，游标历遍全表进行插入和更新
#3
如果是sql 2000,2005得分两步.
1.
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#4
如果是2008,用MERGE,参考如下:
解读SQL Server2008的新语句MERGE
作者：Jonathan Allen 2007-07-24
SQL Server 2008将包含用于合并两个行集(rowset)数据的新句法。根据一个源数据表对另一个数据表进行确定性的插入、更新和删除这样复杂的操作，运用新的MERGE语句，开发者用一条命令就可以完成。
对两个表进行信息同步时，有三步操作要进行。首先要处理任何需要插入目标数据表的新行。其次是处理需要更新的已存在的行。最后要删除不再使用的旧行。这个过程中需要维护大量重复的逻辑，并可能导致微妙的错误。
Bob Beauchemin讨论了MERGE语句，这个语句将上述的多个操作步骤合并成单一语句。他给出了如下的例子：
merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"
如你所见，具体操作是根据后面的联合(join)的解析结果来确定的。在这个例子中，如果目标和源数据表有匹配的行，就实行更新操作。如果没有，就实行插入或者删除操作来使目标数据表和源数据表保持一致。
这个新句法的一个美妙之处是它在处理更新时的确定性。在使用标准的UPDATE句法和联合时，可能有超过一个源行跟目标行匹配。在这种情况下，无法预料更新操作会采用哪个源行的数据。
而当使用MERGE句法时，如果存在多处匹配，它会抛出一个错误。这就提醒了开发者，要达到预想的目标，当前的联合条件还不够明确。
SQL Server 2008 MERGE
ZDNet 软件频道 更新时间：2007-11-19 作者：David.Portas 来源：David Portas’ Blog
本文关键词：MERGE SQL Server 2008 SQL Server 数据库
MERGE is a new DML statement in SQL Server 2008. Microsoft have implemented the ISO SQL 2003 and 2007 standard MERGE statement (as seen in Oracle and DB2) and added some extensions of their own.
In a nutshell, MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.
MERGE has loads of possible applications. For the first time you can assign the contents of one table or query to another in a single operation. The following example requires SQL Server 2008 CTP4. Given this schema and data:
CREATE TABLE a
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
CREATE TABLE b
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);
INSERT INTO b VALUES (1,1,1,1),(3,3,3,3);
The following MERGE will populate table a with the same data as table b:
MERGE INTO a
USING b
ON a.keycol = b.keycol
WHEN MATCHED THEN
UPDATE SET
col1 = b.col1,
col2 = b.col2,
col3 = b.col3
WHEN NOT MATCHED THEN
INSERT (keycol, col1, col2, col3)
VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN SOURCE NOT MATCHED THEN
DELETE;
In the relational world this is the operation known as Relational Assignment ie:
a := b
Unfortunately the SQL syntax is less pretty and requires just a little more typing!
MERGE also makes a good "upsert" for application CRUD stored procedures, removing the need for constructs like:
IF NOT EXISTS ...
INSERT ...
Here's an example I created today. It inserts a new Vendor if and only if the name doesn't already exist. Whether the name previously existed or not, it returns the IDENTITY value of the existing or newly inserted row.
CREATE PROC dbo.usp_VendorUpsert
(
@pVendorID INT OUTPUT,
@pVendorName VARCHAR(80)
)
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.Vendor t
USING (SELECT @pVendorName
) p(VendorName)
ON t.VendorName = @pVendorName
WHEN NOT MATCHED THEN
INSERT (VendorName)
VALUES (@pVendorName)
WHEN MATCHED THEN
UPDATE SET @pVendorID = VendorID;
SET @pVendorID = COALESCE(SCOPE_IDENTITY(),@pVendorID);
END
RETURN
It's amazing that it took nearly 20 years for the SQL standards committee to come up with MERGE. Perhaps the delay is a legacy of the decision to make INSERT, UPDATE and DELETE the basic data update operators. INSERT, UPDATE and DELETE can all be defined as different kinds of relational assignment - assignment being the most basic type of update possible. So arguably MERGE is the more primitive and fundamental data update operator that ought to have been around earlier rather than later.
#5
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update table2 set a4=table1.a4,a5=table1.a5 from table1 where table2.a1=table1.a1 and table2.a2=table1.a2 and table2.a3=table1.a3
#6
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
#7
insert into table2
select * from table1
where table1.a1<> table2.a1 and table1.a2<> table2.a2 and table1.a2<> table2.a2
#8
Declare @a1 varchar(50)
Declare @c2 varchar(50)
Declare @a3 varchar(50)
Declare copy_cur Cursor for
Select a1,a2,a3 From table1
Open copy_cur
Fetch Next From copy_cur into @a1,@a2,,@a3
WHILE @@FETCH_STATUS = 0
Begin
if exists(Select * From table2 where a1=@a1 and a2=@a2 and a3=@a3)
begin
update b
set b.a4=a.a4,b.a5=a.a5
from table1 a,table2 b
where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 and b.a1=@a1 and b.a2=@a2 and b.a3=@a3
end
else
begin
insert into table2
select * From table1 where a1=@a1 and a2=@a2 and a3=@a3
end
Fetch Next From copy_cur into @a1,@a2,,@a3
End;
Close copy_cur
Deallocate copy_cur
#9
我在做DTS转档，不能直接转，因为有条件限制，所以得写好Sql语句，我用的是SQl2000
注意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
我写的Insert 部分SQL如下：
注意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
我写的Insert 部分SQL如下：
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1,tb2 where (tb2.a1 <> tb1.a1 and tb2.a2 <> tb1.a2 and tb2.a3 <> tb1.a3)
#10
改为:
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#11
还是提示：不能在对象table2中插入重复键？？？
#12
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#13
--如果列是数字的话
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,0) = isnull(tb1.a1,0) and isnull(tb2.a2,0) = isnull(tb1.a2,0) and isnull(tb2.a3,0) = isnull(tb1.a3,0))
--如果列是字符的话
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#14
默认值不要设为'0'，要设为''
#15
把我的那段遊標放在查詢分析器中一執行就OK了.
#16
再用这个试试。
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#17
帮顶一下
#18
全都谢谢了，按照10楼，12楼的方法，马上就有结果了，等会儿再做答复。
#19
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#20
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
#21
感谢各位朋友的及时帮助，问题很快得以解决，分就这么多，希望大家不要见怪！
#22
d好厚爱
#23
哇塞。又学习了一招。强。顶
#24
.
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#25
路过
#26
学习。。。。。。。。。。
#27
观望中.............
#28
数据转换服务
#29
留名学习中;............
#30
帮顶一下
#31
跟着大家学习哈。呵呵
#32
mark
#33
观望
#34
原因就是table1中的a1,a2,a3三列和table2中的a1,a2,a3三列完全相同了。
解决方法：考虑效率问题。批量修改，添加。尽量少的做表链接和遍历。
方案（供参考）:
1.将两表重复主键的数据备份到临时表里。
2.将临时表的数据更新到table2的a4,a5字段。
3.将table1 插入到table2 条件是table1的主键不在临时表里。
解决方法：考虑效率问题。批量修改，添加。尽量少的做表链接和遍历。
方案（供参考）:
1.将两表重复主键的数据备份到临时表里。
2.将临时表的数据更新到table2的a4,a5字段。
3.将table1 插入到table2 条件是table1的主键不在临时表里。
#35
意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
#36
意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
#37
up
#38
这得mark，记下来
#39
同意用游标，不然不好实现
#40
这真的可以吗
#41
hen hao a
#42
该回复于2009-03-20 09:47:55被版主删除
#43
bangding
#44
顶
#45
了解了解 ～ 顺便顶一下
#46
学习学习！！
#47
方法不错，试试！
#48
学习学习
#49
记下，以前有这种问题再详细看！
#50
该回复于2011-11-21 09:07:24被版主删除
#1
應該用遊標吧.
#2
按你的要求，貌似不能直接导数据吧，
可以用存储过程，游标历遍全表进行插入和更新
可以用存储过程，游标历遍全表进行插入和更新
#3
如果是sql 2000,2005得分两步.
1.
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#4
如果是2008,用MERGE,参考如下:
解读SQL Server2008的新语句MERGE
作者：Jonathan Allen 2007-07-24
SQL Server 2008将包含用于合并两个行集(rowset)数据的新句法。根据一个源数据表对另一个数据表进行确定性的插入、更新和删除这样复杂的操作，运用新的MERGE语句，开发者用一条命令就可以完成。
对两个表进行信息同步时，有三步操作要进行。首先要处理任何需要插入目标数据表的新行。其次是处理需要更新的已存在的行。最后要删除不再使用的旧行。这个过程中需要维护大量重复的逻辑，并可能导致微妙的错误。
Bob Beauchemin讨论了MERGE语句，这个语句将上述的多个操作步骤合并成单一语句。他给出了如下的例子：
merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"
如你所见，具体操作是根据后面的联合(join)的解析结果来确定的。在这个例子中，如果目标和源数据表有匹配的行，就实行更新操作。如果没有，就实行插入或者删除操作来使目标数据表和源数据表保持一致。
这个新句法的一个美妙之处是它在处理更新时的确定性。在使用标准的UPDATE句法和联合时，可能有超过一个源行跟目标行匹配。在这种情况下，无法预料更新操作会采用哪个源行的数据。
而当使用MERGE句法时，如果存在多处匹配，它会抛出一个错误。这就提醒了开发者，要达到预想的目标，当前的联合条件还不够明确。
SQL Server 2008 MERGE
ZDNet 软件频道 更新时间：2007-11-19 作者：David.Portas 来源：David Portas’ Blog
本文关键词：MERGE SQL Server 2008 SQL Server 数据库
MERGE is a new DML statement in SQL Server 2008. Microsoft have implemented the ISO SQL 2003 and 2007 standard MERGE statement (as seen in Oracle and DB2) and added some extensions of their own.
In a nutshell, MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.
MERGE has loads of possible applications. For the first time you can assign the contents of one table or query to another in a single operation. The following example requires SQL Server 2008 CTP4. Given this schema and data:
CREATE TABLE a
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
CREATE TABLE b
(keycol INT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL);
INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);
INSERT INTO b VALUES (1,1,1,1),(3,3,3,3);
The following MERGE will populate table a with the same data as table b:
MERGE INTO a
USING b
ON a.keycol = b.keycol
WHEN MATCHED THEN
UPDATE SET
col1 = b.col1,
col2 = b.col2,
col3 = b.col3
WHEN NOT MATCHED THEN
INSERT (keycol, col1, col2, col3)
VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN SOURCE NOT MATCHED THEN
DELETE;
In the relational world this is the operation known as Relational Assignment ie:
a := b
Unfortunately the SQL syntax is less pretty and requires just a little more typing!
MERGE also makes a good "upsert" for application CRUD stored procedures, removing the need for constructs like:
IF NOT EXISTS ...
INSERT ...
Here's an example I created today. It inserts a new Vendor if and only if the name doesn't already exist. Whether the name previously existed or not, it returns the IDENTITY value of the existing or newly inserted row.
CREATE PROC dbo.usp_VendorUpsert
(
@pVendorID INT OUTPUT,
@pVendorName VARCHAR(80)
)
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.Vendor t
USING (SELECT @pVendorName
) p(VendorName)
ON t.VendorName = @pVendorName
WHEN NOT MATCHED THEN
INSERT (VendorName)
VALUES (@pVendorName)
WHEN MATCHED THEN
UPDATE SET @pVendorID = VendorID;
SET @pVendorID = COALESCE(SCOPE_IDENTITY(),@pVendorID);
END
RETURN
It's amazing that it took nearly 20 years for the SQL standards committee to come up with MERGE. Perhaps the delay is a legacy of the decision to make INSERT, UPDATE and DELETE the basic data update operators. INSERT, UPDATE and DELETE can all be defined as different kinds of relational assignment - assignment being the most basic type of update possible. So arguably MERGE is the more primitive and fundamental data update operator that ought to have been around earlier rather than later.
#5
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update table2 set a4=table1.a4,a5=table1.a5 from table1 where table2.a1=table1.a1 and table2.a2=table1.a2 and table2.a3=table1.a3
#6
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
#7
insert into table2
select * from table1
where table1.a1<> table2.a1 and table1.a2<> table2.a2 and table1.a2<> table2.a2
#8
Declare @a1 varchar(50)
Declare @c2 varchar(50)
Declare @a3 varchar(50)
Declare copy_cur Cursor for
Select a1,a2,a3 From table1
Open copy_cur
Fetch Next From copy_cur into @a1,@a2,,@a3
WHILE @@FETCH_STATUS = 0
Begin
if exists(Select * From table2 where a1=@a1 and a2=@a2 and a3=@a3)
begin
update b
set b.a4=a.a4,b.a5=a.a5
from table1 a,table2 b
where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 and b.a1=@a1 and b.a2=@a2 and b.a3=@a3
end
else
begin
insert into table2
select * From table1 where a1=@a1 and a2=@a2 and a3=@a3
end
Fetch Next From copy_cur into @a1,@a2,,@a3
End;
Close copy_cur
Deallocate copy_cur
#9
我在做DTS转档，不能直接转，因为有条件限制，所以得写好Sql语句，我用的是SQl2000
注意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
我写的Insert 部分SQL如下：
注意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
我写的Insert 部分SQL如下：
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1,tb2 where (tb2.a1 <> tb1.a1 and tb2.a2 <> tb1.a2 and tb2.a3 <> tb1.a3)
#10
改为:
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#11
还是提示：不能在对象table2中插入重复键？？？
#12
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#13
--如果列是数字的话
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,0) = isnull(tb1.a1,0) and isnull(tb2.a2,0) = isnull(tb1.a2,0) and isnull(tb2.a3,0) = isnull(tb1.a3,0))
--如果列是字符的话
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#14
默认值不要设为'0'，要设为''
#15
把我的那段遊標放在查詢分析器中一執行就OK了.
#16
再用这个试试。
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#17
帮顶一下
#18
全都谢谢了，按照10楼，12楼的方法，马上就有结果了，等会儿再做答复。
#19
insert into tb2(a1,a2,a3,a4,a5)
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
select tb1.a1,tb1.a2,tb1.a3,tb1.a4,tb1.a5 from tb1 where not exists(select 1 from tb2 where isnull(tb2.a1,'') = isnull(tb1.a1,'') and isnull(tb2.a2,'') = isnull(tb1.a2,'') and isnull(tb2.a3,'') = isnull(tb1.a3,''))
#20
insert table2 select * from table1 a where not exists(select 1 from table2 where a1=a.a1 and a2=a.a2 and a3=a.a3)
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
update a set a4=b.a4,a5=b.a5 from table2 a, table1 b where a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3
#21
感谢各位朋友的及时帮助，问题很快得以解决，分就这么多，希望大家不要见怪！
#22
d好厚爱
#23
哇塞。又学习了一招。强。顶
#24
.
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
update tb2
set a4 = tb1.a4, a5 = tb1.a5
from tb2 , tb1
where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3
2.
insert into tb2 select * from tb1 where not exists(select 1 from tb1 where tb2.a1 = tb1.a1 and tb2.a2 = tb1.a2 and tb2.a3 = tb1.a3)
#25
路过
#26
学习。。。。。。。。。。
#27
观望中.............
#28
数据转换服务
#29
留名学习中;............
#30
帮顶一下
#31
跟着大家学习哈。呵呵
#32
mark
#33
观望
#34
原因就是table1中的a1,a2,a3三列和table2中的a1,a2,a3三列完全相同了。
解决方法：考虑效率问题。批量修改，添加。尽量少的做表链接和遍历。
方案（供参考）:
1.将两表重复主键的数据备份到临时表里。
2.将临时表的数据更新到table2的a4,a5字段。
3.将table1 插入到table2 条件是table1的主键不在临时表里。
解决方法：考虑效率问题。批量修改，添加。尽量少的做表链接和遍历。
方案（供参考）:
1.将两表重复主键的数据备份到临时表里。
2.将临时表的数据更新到table2的a4,a5字段。
3.将table1 插入到table2 条件是table1的主键不在临时表里。
#35
意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
#36
意：以前表中a1,a2两列是主键，现在又增加了一列a3作为主键
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
是不是和这个有关系呢，a3是我新增加的列又把它做为其中的一个联合主键之一，默认值设为了'0'
#37
up
#38
这得mark，记下来
#39
同意用游标，不然不好实现
#40
这真的可以吗
#41
hen hao a
#42
该回复于2009-03-20 09:47:55被版主删除
#43
bangding
#44
顶
#45
了解了解 ～ 顺便顶一下
#46
学习学习！！
#47
方法不错，试试！
#48
学习学习
#49
记下，以前有这种问题再详细看！
#50
该回复于2011-11-21 09:07:24被版主删除