SQL Server 2008中的MERGE语句

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

我有一个表student(int ID,nvarchar(10)studentName)和一个表用户类型myStudentType(int ID,nvarchar(10)studentName)

I need to create a stored procedure that accepts a myStudentType parameter (@students) and a parameter @newname

我需要创建一个接受myStudentType参数(@students)和参数@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

并将myStudentType与student合并,以便两个表中存在id的所有行现在都在student表中,对于studentName - @newname(参数),sp将返回来自@students的所有记录的列表在学生表中更新

What is the best way to do that (preferred with merge)

最好的方法是什么(首选合并)

thanks

1 个解决方案

#1

The part of the spec that I think will be most problematic is:

我认为最有问题的规范部分是:

the sp will return a list of all the records from @students that were not updated in the student table

sp将返回@students中未在student表中更新的所有记录的列表

I assume by 'list' the spec implies 'table' (T-SQL has no concept of a 'list' type). An output parameter cannot be a TABLE type (including user-defined table types). While a function can return a table, you cannot update base tables via a function. For more details and possible solutions, see How to Share Data Between Stored Procedures by by Erland Sommarskog.

我假设'list'规范暗示'table'(T-SQL没有'list'类型的概念)。输出参数不能是TABLE类型(包括用户定义的表类型)。函数可以返回表,但不能通过函数更新基表。有关更多详细信息和可能的解决方案,请参阅Erland Sommarskog如何在存储过程之间共享数据。