如何实现A表->B表,数据存在则更新?

[lokiice]

比方说有A B 2个数据表,通过 Code Type Time 来识别记录
从A表Select数据Insert到B表时

INSERT INTO B
SELECT Code,Type,Time
FROM A

如果B表中存在相同记录则替换/更新改记录
怎么实现?

[fengfan]

追加可以写:

insert into b select code ,type,time from a where not in (select code,type,time from b )

替换和更新我能想到的就是写过程了.

[fengfan]

open cursor for
select code,type,time form a where in (select code type,time from b )

[linguojin11]

INSERT INTO B
SELECT Code,Type,Time
FROM A
where not exists (select 1 from b where A.code=b.code and a.type=b.type)

[水族杰纶]

IF EXISTS(SELECT 1 FROM A ,B WHERE A.CODE=B.CODE)
UPDATE B SET TYPE=T.TYPE,TIME=T.TYPE FROM TA WHERE TA.CODE=B.CODE
ELSE 
INSERT INTO B SELECT Code,Type,Time FROM A

[moonshineidolon]

update b

from a inner join b on a.code = b.code
set b.time=a.time

[lokiice]

好像没说明白
我问的是,从A表Select 的出数据 插入表B
也就是说，我葱花A表中查出的数据数据，可能在B标中存在相同键值（Type，Code，Time 类型编号时间相同)，也可能没有相同的
相同的部分替换掉，不相同的部分直接插入

[dawugui]

[Quote=引用 6 楼 lokiice 的回复:]
好像没说明白
我问的是,从A表Select 的出数据 插入表B
也就是说，我葱花A表中查出的数据数据，可能在B标中存在相同键值（Type，Code，Time 类型编号时间相同)，也可能没有相同的
相同的部分替换掉，不相同的部分直接插入
[/Quote]

2005,2000分两步

1.update
updte b set
col1 = a.col1,
col2 = a.col2,
...
coln = a.coln
from b , a
where b.type = a.type and b.code = a.code and b.time = a.time and a的其他条件

2.insert
insert into b(需要的字段) select 需要的字段 from a where ... and not exists(select 1 from b where b.type = a.type and b.code = a.code and b.time = a.time)

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.

[百年树人]

[Quote=引用 6 楼 lokiice 的回复:]
好像没说明白
我问的是,从A表Select 的出数据 插入表B
也就是说，我葱花A表中查出的数据数据，可能在B标中存在相同键值（Type，Code，Time 类型编号时间相同)，也可能没有相同的
相同的部分替换掉，不相同的部分直接插入
[/Quote]

直接把有相同的删除，重新插入就可以了。

if exists(select 1 from A,B where a.Type=b.Type and a.Code=b.Code and a.Time=b.Time)
  delete B from A where a.Type=b.Type and a.Code=b.Code and a.Time=b.Time

insert into B SELECT * FROM A

[ai_li7758521]

insert into B
select A.* from A
where not exists (select 1 from B where A.Code=B.Code and A.Type=B.Type and A.Time =B.Time)

update B set 更改列1 = a.更改列1, 更改列2 = a.更改列2
from  A 
where A.Code=B.Code and A.Type=B.Type and A.Time =B.Time