September 21, 2011 at 11:45 am
I have a two tables, emp1, emp2.
EMP1
------
empid empname number city
------- ----------- -------- -----
1 abc 987 delhi
2 xyz 987 noida
3 mnp blore
4 def chennai
EMP2
------
empid empname number city
------- ----------- -------- -----
3 mnp 654 blore
5 x12 noida
4 def 159 chennai
6 d09 chennai
1 abc 987 delhi
Now I want to update emp1 table with emp2 data. The columns matching should be updated and also those not appearing in emp1 should be added to emp1. There should be not any duplicate row in emp1.
I can do it with MERGE statement in sql server 2008 but I dont want to do with it. Please give the solution which works in sql server 2005 and 2008 too.
September 21, 2011 at 12:21 pm
This would be done using an inner join.
Thanks,
Jared
Jared
CE - Microsoft
September 21, 2011 at 12:34 pm
I find it interesting that you know what the MERGE statement is, yet you do not know how to update using an inner join. Is this a homework question?
Jared
Jared
CE - Microsoft
September 21, 2011 at 6:57 pm
But inner join maps only the rows that are matching in the two tables. I also want to update those rows that are not in emp1 but in emp2.
September 21, 2011 at 7:41 pm
You then do an outer join for insert.
Jared
Jared
CE - Microsoft
September 21, 2011 at 10:24 pm
you can do it through an outer join.
it might look like
update emp1 set emp1.col1=emp2.col1,emp1.col2=emp2.col2
from emp2 left join emp1 on emp2.id=emp1.id
September 22, 2011 at 1:23 am
Refer this code
----------------------
CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
INSERT INTO #tblSource VALUES (1,1,'a11')
INSERT INTO #tblSource VALUES (2,1,'a21')
INSERT INTO #tblSource VALUES (3,1,'a31')
INSERT INTO #tblSource VALUES (4,1,'a41')
INSERT INTO #tblTarget VALUES (1,1,'a1')
INSERT INTO #tblTarget VALUES (3,1,'a3')
INSERT INTO #tblTarget VALUES (5,1,'a5')
INSERT INTO #tblTarget VALUES (6,2,'b2')
INSERT INTO #tblTarget VALUES (7,2,'b1')
MERGE #tblTarget
USING (
SELECT *
FROM #tblSource
) AS tblSource
ON tblSource.GroupID = #tblTarget.GroupID
AND tblSource.ID = #tblTarget.ID
WHEN MATCHED THEN
UPDATE
SET Name = tblSource.Name
WHEN NOT MATCHED THEN
INSERT (
ID
, GroupID
, Name
)
VALUES (
tblSource.ID
, tblSource.GroupID
, tblSource.Name
)
WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
THEN DELETE;
SELECT * FROM #tblSource
SELECT * FROM #tblTarget order by GroupID, ID
DROP TABLE #tblSource
DROP TABLE #tblTarget
September 22, 2011 at 3:13 am
Hi,
You can do it by creating SProc.
ALTER PROCEDURE UPDATE_INSERT
AS
BEGIN
UPDATE EMP1
SET EMP1.EMPANME = B.EMPANME, EMP1.EMPCODE = B.EMPCODE, EMP1.CITY = B.CITY
FROM EMP1 A, EMP2 B
WHERE A.EMPID = B.EMPID
INSERT INTO EMP1
SELECT * FROM EMP2 A
WHERE NOT EXISTS (SELECT * FROM EMP1 B
WHERE A.EMPID = B.EMPID)
END
Regards
Palash Gorai
September 22, 2011 at 5:19 am
palash.gorai (9/22/2011)
Hi,You can do it by creating SProc.
ALTER PROCEDURE UPDATE_INSERTAS
BEGIN
UPDATE EMP1
SET EMP1.EMPANME = B.EMPANME, EMP1.EMPCODE = B.EMPCODE, EMP1.CITY = B.CITY
FROM EMP1 A, EMP2 B
WHERE A.EMPID = B.EMPID
INSERT INTO EMP1
SELECT * FROM EMP2 A
WHERE NOT EXISTS (SELECT * FROM EMP1 B
WHERE A.EMPID = B.EMPID)
END
Regards
Palash Gorai
All great solutions, but to avoid putting a query into your where statement you should do this:
INSERT INTO EMP1 (EMPNAME, EMPCODE, CITY)
SELECT EMP2.EMPNAME, EMP2.EMPCODE, EMP2.CITY
FROM EMP2
LEFT OUTER JOIN EMP1
ON EMP2.EMPID = EMP1.EMPID
WHERE EMP1.EMPNAME IS NULL
This should be the better solution for the insert part. A full set of insert, updates, and deleted would look like this:
INSERT INTO EMP1 (EMPNAME, EMPCODE, CITY)
SELECT EMP2.EMPNAME, EMP2.EMPCODE, EMP2.CITY
FROM EMP2
LEFT OUTER JOIN EMP1
ON EMP2.EMPID = EMP1.EMPID
WHERE EMP1.EMPNAME IS NULL
UPDATE EMP1
SET EMPNAME = EMP2.EMPNAME, EMPCODE = EMP2.EMPCODE, CITY = EMP2.CITY
FROM EMP1
INNER JOIN EMP2
ON EMP1.EMPID = EMP2.EMPID
WHERE EMP1.EMPNAME <> EMP2.EMPNAME
OR EMP1.EMPCODE <> EMP2.EMPCODE
OR EMP1.CITY <> EMP2.CITY
DELETE EMP1
FROM EMP1
LEFT OUTER JOIN EMP2
ON EMP1.EMPID = EMP2.EMPID
WHERE EMP2.EMPNAME ID NULL
Thanks,
Jared
Jared
CE - Microsoft
September 22, 2011 at 6:06 am
Thanks all for replying. I did like this.
UPDATE e1
SET e1.empname = e2.empname,
e1.number = e2.number,
e1.city = e2.city
FROM emp1 as e1
JOIN emp2 as e2 on e1.empid = e2.empid
INSERT INTO emp1 (empid, empname, number, city)
SELECT empid, empname, number, city
FROM Emp2 AS e2
WHERE NOT EXISTS (SELECT 1 FROM Emp1 AS e1 WHERE e1.empid = e2.empid)
September 22, 2011 at 7:24 am
abhijay.singh (9/22/2011)
Thanks all for replying. I did like this.UPDATE e1
SET e1.empname = e2.empname,
e1.number = e2.number,
e1.city = e2.city
FROM emp1 as e1
JOIN emp2 as e2 on e1.empid = e2.empid
INSERT INTO emp1 (empid, empname, number, city)
SELECT empid, empname, number, city
FROM Emp2 AS e2
WHERE NOT EXISTS (SELECT 1 FROM Emp1 AS e1 WHERE e1.empid = e2.empid)
You may 'like' this, but it is not the accepted nor optimized way to do this. The insert should be using an outer join, not a subquery. If this data set becomes very large, you will have performance issues.
Jared
Jared
CE - Microsoft
September 23, 2011 at 12:33 am
Hi,
NOT EXISTS is the most optimized way of coding.
Run on huge DB and check the execution plan. The CPU time of the LEFT OUTER JOIN will be higher than the NOT EXISTS.
Regards
Palash Gorai
September 23, 2011 at 7:52 am
From my experience and the execution plan results on a table with over 4 million rows, I see much better performance with the left outer join. I guess like many things, it depends on indexed columns, NULLS, and size of data. Thanks for making me do more research and testing 🙂 Like many things, you must look at your situation and check both queries for performance on your data.
Thanks,
Jared
Jared
CE - Microsoft
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply