CODEWITHSUNDEEP
sql-server-2008t-sql
Chris Mayers
Chris Mayers

Reputation: 17

Compare two tables and Insert/Update data

I have two tables as below what i need to do is Loop through Table2 rows and need to check if table1ID in Table2 exists in table1. If exists update existing table1 row active flag to 0 and insert a new record in table1 with values from Table2 row. If does not exist insert a new record in table1 with values from Table2 row. We can assume E1, E2,E3 columns correspond to Q1A, Q2A, Q3A in table1.

Table1:
Table1ID   Q1A    Q2A      Q3A    Active 
1           2      Test     1       1
2           3      Test2    1       1
3           4      Test3    1       1
4           5      Test4    1       1


Table2:
Table1ID   E1       E2          E3      
1           2      TestData1     1       
2           3      TestData2     1      
3           4      TestData3     1      
5           5      TestData5     1     
6           7      TestData6     0

Upvotes: 1

Views: 1888

Answers (2)

SouravA
SouravA

Reputation: 5243

If exists update existing table1 row active flag to 0 and insert a new record in table1 with values from Table2 row.

If does not exist insert a new record in table1 with values from Table2 row

From what you wrote, it seems you want to update the Active column to 0 for an existing record and insert the records from Table2 in any case.

Below should work for you:

MERGE Table1 t1
USING Table2 t2
ON t1.Table1ID = t2.Table1ID
WHEN MATCHED THEN
  UPDATE
  SET t1.Active = 0;

INSERT (Table1ID, Q1A, Q2A, Q3A, Active)
VALUES (t2.Table1ID, E1, E2, E3, 1);

Upvotes: 1

Nick Pfitzner
Nick Pfitzner

Reputation: 206

So in both cases, if the record exists, you're going to insert a row from Table2. The only difference is, if it exists in Table1, you want to set it to zero.

DECLARE @currid INT, @maxid INT, @lastid INT

SELECT @currid = 0
, @lastid = 0
, @maxid = MAX(Table1ID) 
FROM Table2

WHILE (@currid < @maxid)
BEGIN
  -- Get the next minimum Table1ID from Table2
  SELECT @currid = MIN(Table1ID)
  FROM Table2
  WHERE Table1ID > @lastid

  -- See if it exists in Table1
  IF EXISTS (SELECT 1 FROM Table1 WHERE Table1ID = @currid)
  BEGIN
    -- Set Active to zero as specified
    UPDATE Table1ID
    SET Active = 0
    WHERE Table1ID = @currid
  END

  -- Copy row from Table2 - I assume with all columns as well as 1 for Active
  INSERT INTO Table1
  SELECT *, 1
  FROM Table2
  WHERE Table1ID = @currid

  -- Set our ID value for the next loop
  SET @lastid = @currid
END

Upvotes: 1

Related Questions