drop table Products --Target Table)

drop table UpdatedProducts--Source Table --Update records

--Create a target(Destination) table

CREATE TABLE Products

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

)

GO

--Insert records into target(Destination) table

INSERT INTO Products VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 20.00),

(3, 'Muffin', 30.00),

(4, 'Biscuit', 40.00)

GO

--Create source table

CREATE TABLE UpdatedProducts

(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Rate MONEY

)

GO

--Insert records into source table

INSERT INTO UpdatedProducts VALUES

(1, 'Tea', 10.00),

(2, 'Coffee', 25.00),

(3, 'Muffin', 35.00),

(5, 'Pizza', 60.00)

GO

SELECT * FROM Products--Destination table

SELECT * FROM UpdatedProducts--Source Table

SELECT * FROM Products left outer join UpdatedProducts

on Products.ProductID=UpdatedProducts.ProductID

GO

/******************************************************************************/

/********************************** Note:-*************************************/

/******************************************************************************/

/* After executing above tables and Records then unComment following Script */

/*Update records in Destination table*/

--UPDATE Products --Target (Destination table)

--SET Products.ProductName =UpdatedProducts.ProductName,

--Products.Rate=UpdatedProducts.Rate

--FROM Products

--INNER JOIN UpdatedProducts

--ON Products.ProductID = UpdatedProducts.ProductID

--/* Insert New records in destination table */

--INSERT INTO Products (ProductID,ProductName,Rate)--New records

--SELECT UpdatedProducts.ProductID,UpdatedProducts.ProductName,UpdatedProducts.Rate

--FROM UpdatedProducts

--LEFT OUTER JOIN Products

--ON UpdatedProducts.ProductID = Products.ProductID

--WHERE Products.ProductID IS NULL

/******************************************************************************/

/********************************** Note:-*************************************/

/******************************************************************************/

Thanks & Regards

Ramesh Nagineni

Infosys Technologies

Hyderabad