update from one table to another if doesnt exist in the original table in sql server 2005

I have 2 tables with the same columns - T1, T2. I want to update table T1 columns from the columns that exist in T2 based on the key column: if the key column exists then 开发者_运维技巧update the rest of the columns of T1 from T2, if doesnt exist, then insert the whole row from T2 to T1.

This query doesnt do the job:

IF EXISTS (SELECT keyC FROM T2 WHERE keyC in (select keyC from T1)) 
UPDATE T1 SET T1.c1 = T2.c1,
T1.c2 = T2.c2,
from  T2 WHERE  T2.keyC in (select keyC from T1)
ELSE (INSERT INTO T1 select * from T2)

Any idea how to fix it?

Thanks in advance,

Greg

Since you use SQL Server 2005 you can't use merge and have to do two statements. One update and one insert.

declare @T1 table (keyC int, c1 int)
declare @T2 table (keyC int, c1 int)

insert into @T1 values (1, 1)

insert into @T2 values (1, 10)
insert into @T2 values (2, 20)

-- Update all rows
update @T1 set
  c1 = T2.c1
from @T1 as T1
  inner join @T2 as T2
    on T1.keyC = T2.keyC

-- Insert new rows
insert into @T1 (keyC, c1)
select keyC, c1
from @T2 as T2
where not exists (select *
                  from @T1 as T1
                  where T1.keyC = T2.keyC)

In SQL2008 you can use the elegant MERGE statement:

MERGE T1 AS target
USING T2 AS source ON (target.KeyC = source.KeyC)
WHEN MATCHED THEN 
    UPDATE SET  c1 = source.c1,
                c2 = source.c2
WHEN NOT MATCHED THEN   
    INSERT (c1, c2)
    VALUES (source.c1, source.c2);

For SQL Server 2008, take a look at using the MERGE statement.

MERGE T1 AS target
USING T2 AS source
ON (target.keyC = source.keyC)
WHEN MATCHED THEN 
    UPDATE SET c1 = source.c1, c2 = source.c2
WHEN NOT MATCHED THEN   
    INSERT (keyC, c1, c2)
    VALUES (source.keyC, source.c1, source.c2)

EDIT: OP changed question from 2008 to 2005

For earlier versions, you need 2 operations:

UPDATE target
    SET c1 = source.c1, c2 = source.c2
    FROM T1 AS target
        INNER JOIN T2 as source
            ON target.keyC = source.keyC

INSERT INTO T1
    (keyC, c1, c2)
    SELECT keyC, c1, c2
        FROM T2
        WHERE NOT EXISTS(SELECT NULL FROM T1 WHERE keyC = T2.keyC)