MS SQL Server最后插入ID

In my database all tables are using a common table for Sequence(ID_Table).

在我的数据库中，所有表都使用一个公共表作为序列(ID_Table)。

TABLE_ID has two fields (Common_ID, Table_Name).

TABLE_ID有两个字段(Common_ID、Table_Name)。

If I insert any record in the table, I have to first insert a record in Table_ID(Auto-increment, Table_name) then use that Auto-increment value in my Other Table.

如果我在表中插入任何记录，我必须首先在Table_ID(自动递增，Table_name)中插入一条记录，然后在另一个表中使用这个自动递增值。

For example, I want to insert in Table_Products which has fields ID(Common_ID), Product_Name, Product_ID(Auto Increment)

例如，我想要在Table_Products中插入具有字段ID(Common_ID)、Product_Name、Product_ID(自动递增)的字段

I want to do something like this:

我想做这样的事情:

INSERT INTO TABLE_ID (Table_NAME), Values (Table_Products)

Get the Inserted ID and use it in Table_Products:

获取插入的ID并在Table_Products中使用它:

INSERT INTO Table_Products (ID, Product_Name, Product_ID(Auto Increment) 
VALUES (ID from TABLE_ID, SomeProduct, Increment)

7 个解决方案

#1

Try this one -

试试这个,

DECLARE @ID BIGINT

INSERT INTO dbo.TABLE_ID (Table_NAME) 
SELECT 'Table_Products'

SELECT @ID = SCOPE_IDENTITY()

INSERT INTO dbo.Table_Products (ID, Product_Name)
SELECT @ID, 'SomeProduct'

#2

You can use an insert statement with the output clause to generate a new Common_ID. Using insert ... select, you can specify that ID in an insert operation:

您可以使用带有输出子句的insert语句来生成一个新的Common_ID。使用插入……选择，您可以在插入操作中指定该ID:

declare @Common_ID as table(ID int)

insert  Table_ID
        (Table_Name)
output  inserted.Common_ID into @Common_ID
values  ('Table_Products')

insert  Table_Products
        (ID, Product_Name)
select  ID
,       'Some Product'
from    @Common_ID

#3

Use SCOPE_IDENTITY() after ur insert statementto get the last inserted id.

在您插入语句之后使用SCOPE_IDENTITY()获取最后插入的id。

DECLARE @Product_Id int

INSERT INTO TABLE_ID (Table_NAME) VALUES (Table_Products);
SELECT @Product_Id=SCOPE_IDENTITY();

Insert INTO Table_Products (ID, Product_Name)
VALUES (ID from TABLE_ID, 'SomeProduct')

#4

Dear friend you have to select id of last record inserted and then pass it in another table so bellow code will help you very well

亲爱的朋友，您必须选择插入的最后一个记录的id，然后将它传递到另一个表中，因此下面的代码将会很好地帮助您

Insert INTO TABLE_ID (Table_NAME), Values (Table_Products)
DECLARE @ID int;
set @ID = SCOPE_IDENTITY(); 

Insert INTO Table_Products (ID, Product_Name) 
Values (@ID, SomeProduct)

this code will solve your problem i define @ID for your last record id and then insert it in your other table

这段代码将解决您的问题:我为您的上一个记录id定义@ID，然后将其插入到另一个表中

#5

For MySql use select LAST_INSERT_ID();

对于MySql，选择LAST_INSERT_ID();

#6

All the other answers so far declare intermediary variables for SCOPE_IDENTITY(), but it could be simpler:

到目前为止，所有其他答案都声明了SCOPE_IDENTITY()的中间变量，但它可能更简单:

INSERT INTO dbo.TABLE_ID (Table_NAME) VALUES 'Table_Products';

INSERT INTO dbo.Table_Products (ID, Product_Name) VALUES (SCOPE_IDENTITY(),'SomeProduct');

#7

-1

You could be also more table-specific using IDENT_CURRENT()

您还可以使用IDENT_CURRENT()更具体地指定表

Insert INTO TABLE_ID (Table_NAME), Values (Table_Products)

select @NewID=IDENT_CURRENT('TABLE_ID')

Insert INTO Table_Products (ID, Product_Name, Product_ID
Values (@NewID, SomeProduct, Increment)

#1