I will explain problem with an example:
我将用一个例子来解释问题:
There is two table in my database, named entry, tags
我的数据库中有两个表，名为entry, tags
There is a column named ID_ENTRY in both table. When I add a record to table, entry, I have to take the ID_ENTRY of last added record and add it to table, tags. How can I do it?
两个表中都有一个名为ID_ENTRY的列。当我向表、条目添加一个记录时，我必须获取最后添加记录的ID_ENTRY并将其添加到表、标记。我怎么做呢?
4 个解决方案
#1
0
Immediatly after executing the insert statement on first table, you should query @@IDENTITY doing "SELECT @@identity". That will retrieve the last autogenerated ID... and then just insert it on the second table.
在第一个表上执行insert语句之后，您应该立即查询@@IDENTITY，以“选择@@IDENTITY”。这将检索最后一个自动生成的ID…然后把它插入到第二个表中。
If you are using triggers or something that inserts rows... this may be not work. Use Scope_Identity() instead of @@IDENTITY
如果您正在使用触发器或插入行的东西……这可能行不通。使用Scope_Identity()而不是@@IDENTITY
#2
4
The only way to do this is with multiple statements. Using dynamic sql you can do this by separating each statement in your query string with a semi-colon:
唯一的方法是使用多个语句。使用动态sql，您可以通过使用分号分隔查询字符串中的每个语句:
"DECLARE @ID int;INSERT INTO [Entry] (...) VALUES ...; SELECT @ID = scope_identity();INSERT INTO [TAGS] (ID_ENTRY) VALUES (@ID);"
Make sure you put this in a transaction to protect against concurrency problems and keep it all atomic. You could also break that up into two separate queries to return the new ID value in the middle if you want; just make sure both queries are in the same transaction.
确保您将它放在事务中，以防止并发问题，并保持它是原子的。您还可以将其分解为两个单独的查询，以便在中间返回新的ID值;只要确保两个查询都在同一个事务中。
Also: you are using parameterized queries with your dynamic sql, right? If you're not, I'll personally come over there and smack you 10,000 times with a wet noodle until you repent of your insecure ways.
另外:您正在动态sql中使用参数化查询，对吗?如果你不是，我会亲自到那里，用湿面条打你一万次，直到你后悔自己的不安全的方法。
#3
0
I would probably do this with an INSERT trigger on the named entry table, if you have all of the data you need to push to the tags table available. If not, then you might want to consider using a stored procedure that creates both inside a transaction.
如果您有需要推送到tags表的所有数据，那么我可能会在命名入口表上使用INSERT触发器来实现这一点。如果不是，那么您可能需要考虑使用在事务内部同时创建两个存储过程的存储过程。
If you want to do it in code, you'll need to be more specific about how you are managing your data. Are you using DataAdapter, DataTables, LINQ, NHibernate, ...? Essentially, you need to wrap both inserts inside a transaction of some sort so that either inserts get executed or neither do, but the means to doing that depend on what technology you are using to interact with the database.
如果想要在代码中实现，您需要更具体地说明如何管理数据。你使用DataAdapter, DataTables, LINQ, NHibernate，…?本质上，您需要在某种事务中封装这两个插入，以便要么执行插入，要么不执行，但实现这一点的方法取决于您使用什么技术与数据库交互。
#4
0
If you use dynamic sql, why not use Linq to Entity Framework, now EF is the recommend data access technology from Microsoft (see this post Clarifying the message on L2S Futures from ADO.NET team blog), and if you do an insert with EF the last identity id will available for you automatically, I use it all the time it's easy.
如果您使用动态sql，为什么不使用Linq到实体框架呢?NET team blog)，如果您使用EF插入最后一个标识id，那么您将会自动地使用它，我一直使用它，这很简单。
Hope this helps!
希望这可以帮助!
Ray.
射线。
#1
0
Immediatly after executing the insert statement on first table, you should query @@IDENTITY doing "SELECT @@identity". That will retrieve the last autogenerated ID... and then just insert it on the second table.
在第一个表上执行insert语句之后，您应该立即查询@@IDENTITY，以“选择@@IDENTITY”。这将检索最后一个自动生成的ID…然后把它插入到第二个表中。
If you are using triggers or something that inserts rows... this may be not work. Use Scope_Identity() instead of @@IDENTITY
如果您正在使用触发器或插入行的东西……这可能行不通。使用Scope_Identity()而不是@@IDENTITY
#2
4
The only way to do this is with multiple statements. Using dynamic sql you can do this by separating each statement in your query string with a semi-colon:
唯一的方法是使用多个语句。使用动态sql，您可以通过使用分号分隔查询字符串中的每个语句:
"DECLARE @ID int;INSERT INTO [Entry] (...) VALUES ...; SELECT @ID = scope_identity();INSERT INTO [TAGS] (ID_ENTRY) VALUES (@ID);"
Make sure you put this in a transaction to protect against concurrency problems and keep it all atomic. You could also break that up into two separate queries to return the new ID value in the middle if you want; just make sure both queries are in the same transaction.
确保您将它放在事务中，以防止并发问题，并保持它是原子的。您还可以将其分解为两个单独的查询，以便在中间返回新的ID值;只要确保两个查询都在同一个事务中。
Also: you are using parameterized queries with your dynamic sql, right? If you're not, I'll personally come over there and smack you 10,000 times with a wet noodle until you repent of your insecure ways.
另外:您正在动态sql中使用参数化查询，对吗?如果你不是，我会亲自到那里，用湿面条打你一万次，直到你后悔自己的不安全的方法。
#3
0
I would probably do this with an INSERT trigger on the named entry table, if you have all of the data you need to push to the tags table available. If not, then you might want to consider using a stored procedure that creates both inside a transaction.
如果您有需要推送到tags表的所有数据，那么我可能会在命名入口表上使用INSERT触发器来实现这一点。如果不是，那么您可能需要考虑使用在事务内部同时创建两个存储过程的存储过程。
If you want to do it in code, you'll need to be more specific about how you are managing your data. Are you using DataAdapter, DataTables, LINQ, NHibernate, ...? Essentially, you need to wrap both inserts inside a transaction of some sort so that either inserts get executed or neither do, but the means to doing that depend on what technology you are using to interact with the database.
如果想要在代码中实现，您需要更具体地说明如何管理数据。你使用DataAdapter, DataTables, LINQ, NHibernate，…?本质上，您需要在某种事务中封装这两个插入，以便要么执行插入，要么不执行，但实现这一点的方法取决于您使用什么技术与数据库交互。
#4
0
If you use dynamic sql, why not use Linq to Entity Framework, now EF is the recommend data access technology from Microsoft (see this post Clarifying the message on L2S Futures from ADO.NET team blog), and if you do an insert with EF the last identity id will available for you automatically, I use it all the time it's easy.
如果您使用动态sql，为什么不使用Linq到实体框架呢?NET team blog)，如果您使用EF插入最后一个标识id，那么您将会自动地使用它，我一直使用它，这很简单。
Hope this helps!
希望这可以帮助!
Ray.
射线。