sql server merge 的用法

科睿思博發表於2017-09-22
CREATE TABLE tTable
    (
      id INT ,
      f1 VARCHAR(10) ,
      f2 VARCHAR(10) ,
      f3 VARCHAR(10)
    )
GO
INSERT  INTO tTable
   SELECT  11 ,12 , 13 , 14
INSERT  INTO tTable
   SELECT  13 ,24 ,25,26

GO

CREATE TABLE tTable2
    (
      id INT ,
      f5 VARCHAR(10) ,
      f6 VARCHAR(10)
    )
GO

CREATE PROC MERGETest
AS
    BEGIN
        MERGE INTO tTable2 T1
        USING( SELECT    id ,f3 ,f2  FROM  tTable  )T2  ON ( T1.id = T2.id )
        WHEN MATCHED 
			THEN  UPDATE SET T1.f5 = T2.f2 ,T1.f6 = T2.f3
        WHEN NOT MATCHED THEN
            INSERT ( id, f5, f6 )
            VALUES ( t2.id, t2.f2, t2.f3 );

    END
GO

  

相關文章