如何检索一个表的身份自动编号以插入到另一个表中 [英] how to retrieve identity auto number of one table to insert in another table

问题描述

我有2张桌子

购买的字段InvoiceNo是身份自动编号

PurchaseProduct具有字段PurchaseProduct_No，这是Purchase表中的身份自动编号和InvoiceNo.

对于并发性，我打算像这样插入

I have 2 tables

Purchase has field InvoiceNo which is identity auto number

PurchaseProduct has fields PurchaseProduct_No which is identity auto number and InvoiceNo from Purchase table

For concurrency I plan to insert like this

sqlTran = conn.BeginTransaction();
try
{
    //insert statments for Purchase
    //insert statments for PurchaseProduct
    sqlTran.Commit();
}
catch (SqlException ex)
{
    sqlTran.Rollback();

}

但是，由于它是自动编号，因此如何获取Purchase表的InvoiceNo值以在InventoryProduct表中插入InvoiceNo?

but how do I get InvoiceNo value of Purchase table to insert InvoiceNo in PurchaseProduct table since it is autonumber?

推荐答案

您可以这样做:

myQuery =将字符插入fubar(fu)值(" bar); SELECT @@ IDENTITY;"

然后，您使用ExecuteScalar(NOT ExecuteNonQuery)执行查询，并将查询结果转换为int64.

在那里，您已将自己的身份插入到其他表中.

You do:

myQuery = "INSERT INTO fubar (fu) VALUES (''bar''); SELECT @@IDENTITY;"

Then you execute the query with ExecuteScalar (NOT ExecuteNonQuery) and cast the result of the query to an int64.

There you''ve got your identity to insert in the other table.

请参阅SCOPE_IDENTITY的文档.

SCOPE_IDENTITY [

See the documentation for SCOPE_IDENTITY.

SCOPE_IDENTITY[^]

Example

CREATE TABLE [dbo].[T1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[field1] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T3](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_REF] [int] NOT NULL,
	[field1] [nchar](10) NOT NULL
) ON [PRIMARY]
GO

注意:这两个SQL语句可以通过一个命令字符串发送到SQL Server.

Note: These two SQL statements can be sent to the SQL Server in one command string.

insert into T1 (field1) VALUES('XX');
insert into T3 (ID_REF,field1) VALUES(SCOPE_IDENTITY(),'YY');

使用SqlCommand"INSERT INTO表名(col1，col2，col3，...)值(val1，val2，val3，...);选择CAST(@@ Identity AS INT);"
然后ExecuteScalar并将结果转换为整数.
仅根据所使用的数据库/版本才需要CAST(... AS INT).

Use SqlCommand "INSERT INTO table-name (col1, col2, col3, ...) VALUES (val1, val2, val3,...);Select CAST(@@Identity AS INT);"
and then ExecuteScalar and cast result as an int.
The CAST(... AS INT) is only necessary depending on which database/version you''re using.

这篇关于如何检索一个表的身份自动编号以插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！