Month: June 2012

[codesyntax lang=”sql”]

DROP TABLE #employee;

DROP TABLE #employee1;

CREATE TABLE #employee (

employee_id integer,

first_name  VARCHAR(20),

last_name   VARCHAR(20),

dept_no     integer,

salary      integer);

INSERT INTO #employee VALUES (1, 'Dan', 'Morgan', 10, 100000);

INSERT INTO #employee VALUES (2, 'Jack', 'Cline', 20, 100000);

INSERT INTO #employee VALUES (3, 'Elizabeth', 'Scott', 20, 50000);

INSERT INTO #employee VALUES (4, 'Jackie', 'Stough', 20, 40000);

INSERT INTO #employee VALUES (5, 'Richard', 'Foote', 20, 30000);

INSERT INTO #employee VALUES (6, 'Joe', 'Johnson', 20, 70000);

INSERT INTO #employee VALUES (7, 'Clark', 'Urling', 20, 90000);

SELECT * FROM #employee;

CREATE TABLE #employee1 (

employee_id integer,

first_name  VARCHAR(20),

last_name   VARCHAR(20),

dept_no     integer,

salary      integer);

INSERT INTO #employee1 VALUES (1, 'Dan', 'Morgan', 10, 100001);

INSERT INTO #employee1 VALUES (2, 'Jack', 'Cline', 20, 100000);

INSERT INTO #employee1 VALUES (3, 'Elizabeth', 'Scott', 20, 50002);

INSERT INTO #employee1 VALUES (4, 'Jackie', 'Stough', 24, 40000);

INSERT INTO #employee1 VALUES (5, 'Richard', 'Foote', 20, 30003);

INSERT INTO #employee1 VALUES (6, 'Joe', 'Johnson', 20, 70004);

INSERT INTO #employee1 VALUES (97, 'Clark', 'Urling', 23, 90000);

SELECT * FROM #employee1;

MERGE INTO #employee E

USING (

  SELECT *

  FROM #employee1) E1

ON (E.employee_id = E1.employee_id)

WHEN MATCHED THEN

  UPDATE SET E.dept_no = E1.dept_no, E.salary = E1.salary

WHEN NOT MATCHED THEN

  INSERT VALUES (E1.employee_id,E1.first_name,E1.last_name,E1.dept_no,E1.salary);

  SELECT * FROM #employee;

  SELECT * FROM #employee1;

[/codesyntax]