如何使用存储过程在多个表中插入多个记录 [英] How to insert multiple records in multiple tables with stored procedure

问题描述

我有一个名为
的表
表格:ITEMS
字段:itemid-> pk
地址
位置
标题



类别
catid-> pk
类别名称


CATEGORY_VALUE
catvalid-> pk
itemid-> fk(ITEMS)
catid-> fk(类别)
catval

我将数据插入项目表，类别表中填充了预定义的值，以后可以增加这些值.

现在，当我在项目表中插入数据时，我还必须在category_value表中插入数据.

我可以在商品ID表上使用MAX函数来实现此功能，然后再次对category_value表执行插入查询

但我想使用完全执行此任务的存储过程.


在sql server中有直接函数可以获取最新插入的记录并将其存储在某些局部变量中，然后我可以编写插入查询
.

I have table called

table:ITEMS
fields:itemid->pk
address
location
title



CATEGORY
catid->pk
categoryname


CATEGORY_VALUE
catvalid->pk
itemid->fk (ITEMS)
catid->fk (CATEGORY)
catval

i insert data into items table and category table is filled with predefined values which can be increased later on.

now when i insert data in items table i also have to insert data in category_value table.

I can implement this using MAX function on ID of items table then again execute insert query on category_value table

but i want to use stored procedure which completely performs this task.


is there a direct function in sql server which can fetch the latest inserted record and store it in some local variable and then i can write insert query
.

推荐答案

不，MAX和类似的东西在生产环境中可能会误导您，因为还有一种称为事务的东西.如果您的主键是身份 [ SCOPE_IDENTITY [ IDENT_CURRENT [ http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ [ ^ ].

如果您的主键不是身份字段，那么您将不得不使用锁和其他奇特的东西-我不建议这样做.

在SQL Server 2012中，最后实现了 SEQUENCE [

No, MAX and such things might mislead you in production environments since there is also something called transaction. If your primary keys are identities[^], you can use SCOPE_IDENTITY[^] and IDENT_CURRENT[^] to get the latest identity generated on a table or in a scope. You probably will need the scope version, since the procedure execution is not atomic.
Read this short article: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[^].

If your primary keys are not identity fields, than you will have to use locks and other fancy things - which I don''t recommend.

In SQL Server 2012 there is at last implemented the SEQUENCE[^] object, which is part of the SQL standard for a very long time...

亲爱的先生，有一种方法可以在单个参数中传递多个值.

例如为sql server参数传递一个数组列表，以便可以在存储过程中对其进行访问

Dear sir is there a way to pass multiple values in a single parameter .

for example passing an array-list for a sql server parameter.so that it can be accessed in stored procedure

这篇关于如何使用存储过程在多个表中插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！