merge problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

merge problem

Author

Topic

bbillybollt
Starting Member

4 Posts

Posted - 2011-12-18 : 10:01:11

I have a table student(int ID , nvarchar(10) studentName )
and a table user type myStudentType(int ID , nvarchar(10) studentName )

I need to crate a stored procedure that acespts a myStudentType paramter (@students ) and a parmeter @newname
mySp(@students myStudentType, @newname nvarchar(10))

and merges myStudentType with student so that all the rows with ids that exists on both tables will now in student table have for the studentName - @newname (the parameter)
and the sp will return a list of all the records from @students that were not updated in
the student table

what is the best way to do that (prefered with merge)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 10:11:06

something like


...

MERGE student AS s
USING myStudentType AS st
ON st.ID = s.ID
AND st.studentName = s.studentName 
WHEN MATCHED THEN UPDATE SET studentName = @studentName; 
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bbillybollt
Starting Member

4 Posts

Posted - 2011-12-18 : 10:15:39

tahnks what about getting all the rows from @students that were not updated in student table?

quote:
Originally posted by visakh16

something like

...

MERGE student AS s
USING myStudentType AS st
ON st.ID = s.ID
AND st.studentName = s.studentName
WHEN MATCHED THEN UPDATE SET studentName = @studentName;
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 10:17:38

[code]
...

MERGE student AS s
USING myStudentType AS st
ON st.ID = s.ID
AND st.studentName = s.studentName
WHEN MATCHED THEN UPDATE SET studentName = @studentName
WHEN UNMATCHED THEN
INSERT (ID, studentName)
VALUES (st.ID, st.studentName);
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bbillybollt
Starting Member

4 Posts

Posted - 2011-12-18 : 10:30:30

Thanks how do i get the rows from myStudentType that were no updated in students table? (i dont need to insert the rows we didnt find)

quote:
Originally posted by visakh16

...

MERGE student AS s
USING myStudentType AS st
ON st.ID = s.ID
AND st.studentName = s.studentName
WHEN MATCHED THEN UPDATE SET studentName = @studentName
WHEN UNMATCHED THEN
INSERT (ID, studentName)
VALUES (st.ID, st.studentName);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 12:00:48

you need to use seperate logic for that using NOT EXISTS or LEFT JOIN


SELECT st.*
FROM myStudentType st
LEFT JOIN student s
ON st.ID = s.ID
AND st.studentName = s.studentName 
WHERE s.ID IS NULL


or

SELECT *
FROM myStudentType st
WHERE NOT EXISTS (SELECT 1 FROM Student WHERE ID = st.ID
AND studentName = st.studentName)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources