Update Table data from another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Table data from another Table

Author

Topic

jkmarvelmani
Starting Member

8 Posts

Posted - 2010-09-07 : 01:04:58

I had two tables. table (tblSrc) contains one column named Name. Another table tbl destination contains three columns(ID, name, recCount). I need to insert record from tblSrc to tbldestination.
Conditions:
1. While copying, if the record not found in the destination then insert and set the recCount to 1.
2. If the record already exists, then increment the recCount by 1.
Can anybody help me to achieve this result.

Thanks
R.Mani

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-07 : 03:32:06

Try this -


CREATE PROC USP_ProcName
AS
BEGIN 

	BEGIN TRAN

	UPDATE tblDestination SET recCount = ISNULL(recCount,0) + 1
	FROM tblDestination D
	INNER JOIN tblSrc S ON D.Name = S.Name

	INSERT INTO tblDestination 
	SELECT S.Name, 1 FROM tblSrc S
	LEFT JOIN tblDestination D ON S.Name = D.Name 
	WHERE D.Name IS NULL

	IF @@ERROR > 0 
	BEGIN
		ROLLBACK
	END
	ELSE 
	BEGIN 
		COMMIT
	END

END