将数据从临时表插入到多个相关表中？
我正在开发一个将数据从 Access 导入到 SQL Server 2008 的应用程序。目前，我正在使用存储过程按记录单独导入数据。我无法进行批量插入或类似的操作，因为数据被插入到两个相关的表中...我有一堆字段进入帐户表（名字、姓氏等）和三个字段每个保险表中都有一条记录，通过在存储过程中使用 SCOPE_IDENTITY 选择的自动递增 AccountID 链接回帐户表。
由于从应用程序到数据库的往返次数较多，性能不是很好。出于这个原因和其他一些原因，我计划改用临时表并从那里导入数据。阅读我解决此问题的选项，对临时表中的数据执行相同插入存储过程的游标是有意义的。然而，光标似乎是邪恶的化身，应该避免。
有没有办法在基于集合的操作中将数据插入到一个表中，检索自动生成的 ID，然后使用相应的 ID 将相同记录的数据插入到另一个表中？或者光标是我唯一的选择吗？
I'm working on an application that imports data from Access to SQL Server 2008. Currently, I'm using a stored procedure to import the data individually by record. I can't go with a bulk insert or anything like that because the data is inserted into two related tables...I have a bunch of fields that go into the Account table (first name, last name, etc.) and three fields that will each have a record in an Insurance table, linked back to the Account table by the auto-incrementing AccountID that's selected with SCOPE_IDENTITY in the stored procedure.
Performance isn't very good due to the number of round trips to the database from the application. For this and some other reasons I'm planning to instead use a staging table and import the data from there. Reading up on my options for approaching this, a cursor that executes the same insert stored procedure on the data in the staging table would make sense. However it appears that cursors are evil incarnate and should be avoided.
Is there any way to insert data into one table, retrieve the auto-generated IDs, then insert data for the same records into another table using the corresponding ID, in a set-based operation? Or is a cursor my only option here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
查看 OUTPUT 子句。您应该能够将其添加到您的 INSERT 语句中以执行您想要的操作。
顺便说一句，如果您需要将未插入第一个表的列输出到第二个表中，请使用 MERGE 而不是 INSERT （如原始问题的注释中所建议的那样），因为它的 OUTPUT 子句支持引用源中的其他列表。否则，将其与 INSERT 一起保留更简单，并且它确实允许您访问插入的标识列。
Look at the OUTPUT clause. You should be able to add it to your INSERT statement to do what you want.
BTW, if you need to output columns into the second table that weren't inserted into the first one, then use MERGE instead of INSERT (as suggested in the comment to the original question) as its OUTPUT clause supports referencing other columns from the source table(s). Otherwise, keeping it with an INSERT is more straightforward, and it does give you access to the inserted identity column.
我正在尝试使用数据绑定将多个记录插入到相关表中。所以，试试这个！
希望这非常有帮助。点击此链接如何将记录插入相关表< /a>.了解更多信息。
I'm having experiment to worked out in inserting multiple record into related table using databinding. So, try this!
Hopefully this is very helpful. Follow this link How to insert record into related tables. for more information.