调用存储过程以插入多个值。

In our application we have a multiline grids which have many records. For inserting or updating we are calling a stored procedure.

在我们的应用程序中，我们有一个多线网格，它有许多记录。对于插入或更新，我们调用存储过程。

As per the current implementation the stored procedure is calling for each line in the grid. For each line it checks the existence in the table. If data is already there, it will update the table else insert new data into the table.

按照当前实现，存储过程调用网格中的每一行。对于每一行，它检查表中是否存在。如果数据已经存在，它将更新表else，向表中插入新的数据。

Instead of calling the procedure for each line, we thought create a table value parameter and pass all the grid values at the same time.

我们不是为每一行调用过程，而是创建一个表值参数，同时传递所有网格值。

My questions are:

我的问题是:

Is it a good approach?

这是一个好方法吗?
How to handle the existence check (for insert or update) if I pass the values as table-valued parameter? Do I need to loop through the table and check it?

如果我将值作为表值参数传递，如何处理存在检查(用于插入或更新)?我需要循环遍历表并检查它吗?
Is it better to have separate stored procedures for insert and update?

是否最好有单独的存储过程来进行插入和更新?

Please provide your suggestions. Thanks in advance.

请提供你的建议。提前谢谢。

4 个解决方案

#1

1) Using TVP is good approach, but send only new or updated rows as TVP, no need to send entire datagrid.

1)使用TVP是很好的方法，但是只发送新的或更新的行作为TVP，不需要发送整个datagrid。

2) For INSERT/UPDATE use MERGE example:

2)插入/更新使用合并示例:

MERGE [dbo].[Contact] AS [Target] 
USING @Contact AS [Source] ON [Target].[Email] = [Source].[Email]
WHEN MATCHED THEN 
   UPDATE SET [FirstName] = [Source].[FirstName],
              [LastName] = [Source].[LastName]
WHEN NOT MATCHED THEN
    INSERT ( [Email], [FirstName], [LastName] )
    VALUES ( [Source].[Email], [Source].[FirstName], [Source].[LastName] );

3) For your case one stored procedure is enough.

对于您的情况，一个存储过程就足够了。

#2

1) TVP is a good approach. And a single stored proc call is more efficient with fewer calls to the Database.

1)TVP是一个很好的方法。一个存储的proc调用更有效，对数据库的调用更少。

2) You haven't made it clear if each row in the grid has some kind of ID column that determines if the data exists in the Table, however assuming there is, make sure that it is indexed then use INSERT INTO and UPDATE statements like this:

2)您还没有说清楚，如果网格中的每一行都有某种ID列来确定数据是否存在于表中，但是假设有，请确保它被索引，然后使用INSERT INTO和UPDATE语句:

To add new rows:

添加新行:

INSERT INTO [grid_table] 
SELECT * FROM [table_valued_parameter]
WHERE [id_column] NOT IN (SELECT [id_column] FROM [grid_table])

To update existing rows:

更新现有的行:

UPDATE gt
SET gt.col_A = tvp.col_A,
    gt.col_B = tvp.col_B,
    gt.col_C = tvp.col_C,
    ...
    gt.col_Z = tvp.col_Z
FROM [grid_table] gt 
INNER JOIN [table_valued_parameter] tvp ON gt.id_column = tvp.id_column

NB:

注:

No need to do an IF EXISTS() or anything as the WHERE and JOIN clauses will run the same checks,so no need to do a 'pre-check' before running each statement.
不需要执行IF exist()或任何操作，因为WHERE和JOIN子句将运行相同的检查，所以不需要在运行每个语句之前执行“预检查”。
This assumes the TVP data isthe same structure as the Table in the database.
这假定TVP数据与数据库中的表具有相同的结构。
YOU MUST make sure the id_column is indexed.
您必须确保id_column被索引。
I've use 'INNER JOIN' instead of just 'JOIN' to make the point it is an inner join
我使用了“内部连接”而不是“连接”来说明它是一个内部连接

3) Using the approach above you just new one stored proc, simple and effective

3)使用上述方法，您只需新建一个存储proc，简单而有效

#3

It's a good approach

这是一个很好的方法
Any way try to put the logic through object level for iterating and checking and finally insert/update in T-SQL. This reduces overhead for RDMS as object level functionality is faster than operations in RDBMS.

任何方法都可以尝试将逻辑通过对象层进行迭代和检查，最后在T-SQL中插入/更新。这减少了RDMS的开销，因为对象级功能比RDBMS中的操作要快。
Dont put too may stored procedures for each type of operation have a minimised procedures with multiple operations based on parameters you send to it.

不要把每个操作的存储过程都放在一个最小化的过程中，并根据发送给它的参数进行多个操作。

Hope it helps!

希望它可以帮助!

#4

Yes, it is a good approach. Calling procedure for each row is bad for performance. TVPs make life easier.
是的，这是一个很好的方法。为每一行调用过程不利于性能。利用状态使生活更容易。
Yes, you can do that check in stored procedure, which should be a simple SELECT on uniqueId in most of the cases.
是的，您可以在存储过程中进行检查，在大多数情况下，这应该是在uniqueId上进行简单的选择。
With this approach, yes, it is better to have both in same stored procedure.
使用这种方法，是的，最好在相同的存储过程中同时使用这两种方法。

#1