T SQL Looping on insert or update

Question

I have two tables.

Table A and Table B. The columns are same.

create table TableA (
    id int
    , name varchar
    , last datetime
)

create table TableB (
    id int
    , name varchar
    , last datetime
)

I m populating table A with mass data. and i would like to either insert or update the data in table A into table B.

I d like to take the data from table A and either insert into table B if id and name doenst match or update if the id and name does match.

I tried some ETL tool but the result was very slow. I have indexing on id and name, I wanted to try this with SQL.

I have the following but not working correct:

SELECT      @id = ID, 
      @name = name, 
      @LSDATE = LastSeen_DateTime   
            FROM DBO.A
IF EXISTS (SELECT ID, name FROM DBO.A
WHERE  @ID = ID AND @name = Name)

begin -- update end else begin --insert end

i guess i need to put this in a loop and not quite sure how I can make this run.

Thanks.

Answer 1

Its probably faster to do it two statements one update and one insert rather than a loop

This statement updates all B rows using the data from A where the ID is the same but the name is different

Update

Update 
    tableB
SET
   name = a.Name
From
   tableB a
   INNER JOIN tableA a
   on b.ID = a.ID 
      and A.Name <> b.Name

This statement inserts all B rows into A where the id doesn't exist in A

INSERT

INSERT INTO
   tableB
(   ID,
    Name
)
SELECT
   a.ID
   a.Name
FROM 
   tableA b
WHERE
   not exists (Select A.ID From tableB a WHERE a.ID = b.ID)

Updated (reversed it from A into B rather than B into A)

Answer 2

If you were using SQL Server 2008 (or Oracle or DB2), then you could use a merge statement.

MERGE B
USING A AS source 
ON (B.ID = source.ID and B.Name = source.Name)
WHEN MATCHED THEN 
    UPDATE SET Last = source.Last
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Last) VALUES (source.ID, source.Name, source.Last)

   -- the following is optional, if you remove it, add a semicolon to the end of the above line. 
   OUTPUT $action, 
   inserted.ID AS SourceID, inserted.Name AS SourceName, 
   inserted.Last AS SourceLast, 
   deleted.ID AS TargetID, deleted.Name AS TargetName, 
   deleted.Last AS TargetLast ;   

The bit with the "output $action" will display what rows are getting updated and what rows are getting updated.

weasel words: I recognize this isn't exactly what you were looking for, but since others may search this topic, it may be helpful for others in the future.