CODEWITHSUNDEEP
sql-server
Anand Oca
Anand Oca

Reputation: 1

Comparing data in two tables,insert if data is not there,update if data is there

I have two tables

SELECT * FROM dbo.VMM_Table_20120210     table 1

and output of this table is 

vehicle_Make        vehicle_model
---------------------------
01                   000
01                   111
01                   112
01                   113
01                   114
01                   115
01                   117

like this upto 993 records r there in the above table and 2nd table is 

SELECT * FROM dbo.TBL_VEHICLE_MODEL_NEW

and output of this table is

vmodel_id    vmodel_vmake_code       vmodel_type    vmodel_code
---------------------------------------------------------------------------
1             01                         t             7AV
2             01                         c             7AE

UPTO 1107 records are there in this table

the requirement is I need to compare vehicle_make with vmodel_vmake_code and vehicle_model with vmodel_code and If data is not there in the 2nd table I need to insert it from the first table if data is there I need to update the data

I need it procedure with cursor in the procedure to loop the each row will u please help me in this situation

Upvotes: 0

Views: 1455

Answers (1)

GarethD
GarethD

Reputation: 69819

I am assuming that your tags are for SQL-Server, and not SQL and Server separately, so I am going to suggest the MERGE operation. There are some details that are pretty unclear from the question, such as what Update to perform when there is a match, and how to get values for vmodel_type and vmodel_Code, so I can't provide a perfect answer, but this should get you started:

MERGE INTO dbo.TBL_VEHICLE_MODEL_NEW t 
USING dbo.VMM_Table_20120210 c 
ON t.vmodel_vmake_code = c.vehicle_Make AND t.vmodel_code = c.vehicle_model 
WHEN MATCHED THEN 
    UPDATE SET vmodel_type = 'A' -- CHANGE TO WHATEVER YOU WANT TO HAPPEN WHEN THE DATA EXISTS IN TABLE 2
WHEN NOT MATCHED THEN 
    INSERT VALUES (c.Vehicle_Make, c.Vehicle_Model, 't', '7AV');
    -- WHATEVER YOU WANT TO HAPPEN WHEN THE RECORD IN TABLE 1 DOES NOT EXIST IN TABLE 2

See MSDN for more on MERGE.

Upvotes: 1

Related Questions