Insert/Update using Tsql from one table to other

Question

Hi Guys,

I would like to insert/Update the record from one table to other. I have attached a screenshot for more info.

All blue color columns can be used for checking whether that record is exists in Table B.

Please advise. Thank you in advance.

Answer 1

Not sure I understand the correction. If you do not insert on existence, how is it that Mark's COMPANY became Microsoft from Oracle? 

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.

Answer 2

Have you tried with MERGE JOIN statement ??? 

Check the next post in the forum, if you have any question you can ask us :)

What is your SQL SERVER version ??

SQL Server 2008 : Merge T-SQL Command

Regards ! !

---

Sergio Sánchez Arias

Answer 3

The Table B should update a Mark's record from Oracle to Microsoft.

Answer 4

Here you go:

MERGE TABLE_B AS TARGET
USING (SELECT * FROM TABLE_A O) Source (ID_A, ADDRESS_A, ZIP_A, PHONE_A, COMPANY_A)
ON (TARGET.ID = SOURCE.ID_A AND TARGET.ADDRESS = SOURCE.ADDRESS_A AND TARGET.ZIP = SOURCE.ZIP_A)
WHEN MATCHED THEN UPDATE SET TARGET.PHONE = SOURCE.PHONE_A, TARGET.COMPANY = SOURCE.COMPANY_A
WHEN NOT MATCHED THEN INSERT(ID, ADDRESS, ZIP, PHONE, COMPANY) VALUES(ID_A, ADDRESS_A, ZIP_A, PHONE_A, COMPANY_A) ;

The aliases are just for your understanding, you can change them.

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.

Answer 5

Try - this Inserts / Updates Table B

DECLARE @tableA TABLE(Id INT IDENTITY(1,1)
	,NAME VARCHAR(15)
	,Adress VARCHAR(20)
	,Zip VARCHAR(15)
	,Phone VARCHAR(15)
	,Company VARCHAR(20))
INSERT INTO @tableA(NAME, Adress, Zip, Phone, Company )
VALUES('John','12 HighStreet','54453','333-333-3333','Apple')
,('Mark','23 5thStreet','34356','444-444-4444','Microsoft'),
('Adam','16 park Street','45332','555-555-5555','IBM')
,('Sam','36 8thStreet','34553','222-222-2222','Java')
SELECT *FROM @tableA
DECLARE @tableB TABLE(Id INT IDENTITY(1,1)
,NAME VARCHAR(15)
,Adress VARCHAR(20)
,Zip VARCHAR(15)
,Phone VARCHAR(15)
,Company VARCHAR(20)
)
INSERT INTO @tableB(NAME, Adress, Zip, Phone, Company )
VALUES('John','12 HighStreet','54453','777-777-7777','Apple')
,('Mark','23 5thStreet','34356','444-444-444','Oracle'),
('Adam','16 park Street','45332','555-555-5555','IBM')
--Before Inserting / Updating
SELECT * FROM @tableB
INSERT INTO @tableB(NAME, Adress, Zip, Phone, Company )
SELECT a.NAME, a.Adress, a.Zip, a.Phone, a.Company
FROM @tableA a
WHERE 0= (SELECT COUNT(*)
				   FROM @tableB b
				   WHERE a.Id=b.Id
						AND a.NAME=b.NAME
						AND a.Adress=b.Adress
						AND a.Zip=b.Zip
					) 
--After inserting new rows
SELECT * FROM @tableB
UPDATE b
SET b.Phone=a.Phone
	,b.Company=a.Company
FROM @tableA a
INNER JOIN @tableB b
	ON a.Id=b.Id
	AND a.NAME=b.NAME
	AND a.Adress=b.Adress
	AND a.Zip=b.Zip
	AND
	((CASE WHEN ((a.Phone IS NULL AND b.Phone IS NULL OR a.Phone = b.Phone))
	 THEN 1 ELSE 0 END) = 0 OR
	(CASE WHEN ((a.Adress IS NULL and b.Adress is NULL OR a.Adress  =  b.Adress))
	THEN 1 ELSE 0 END) = 0)
				
--After updating
SELECT * FROM @tableB
					

---

Narsimha