如何在表上多次插入并从表中分配后从表中获取所有id？ [英] How to fetch all id from table after multiple insertion on a table and assign to table?

问题描述

我将datatable作为输入参数传递给存储过程。我已经为它创建了自定义类型。这是我的存储过程：

I am passing datatable as input parameter to a stored procedure. I have created custom type for it. Here is my stored procedure:

INSERT INTO Employee
                ([Name],[Lname],[Code])
    SELECT [Name],[Lname],@Code
    FROM   @tblEmp A
    WHERE  NOT EXISTS (SELECT 1
                       FROM   Employee B
                       inner join Contactdetail c
                       on cid = B.cid
                       WHERE  B.[Name] = A.[Name]
                              AND B.[Lname] = A.[Lname]
                              AND C.[mobno] = A.[mobno]) 

这里我从datatable获取记录并插入Employee表。数据表包含Name，Lname和mobileno。我想检查Name，Lname和mobileno的组合。如果Employee表中存在它的组合，请不要在Employee.Else Insert.After插入记录后插入记录（[Name]，[Lname]，@ Code） ，我想要所有插入记录的id.Scope_identity给出table的最后一个标识。我想要所有id，它们是新插入表中的，因为在同一个sp中，我想做进一步的处理。



所以我修改了我的代码。现在我正在使用这个

Here I fetching record from datatable and inserting into Employee table. Datatable contain Name,Lname and mobileno. I want to check combination of Name,Lname and mobileno.If combination of it present in Employee table,pls don't insert record([Name], [Lname], @Code ) in Employee.Else Insert.After inserting record from Employee,I want id of all inserted record.Scope_identity give last identity of table.I want all id,which are newly inserted into table because in same sp,I want to do further processing.

So I modified my code.Now I am using like this

DECLARE @InsertedIDs table(EmployeeID int);

INSERT INTO Employee
                ([Name],[Lname],[Code])
             --
             OUTPUT INSERTED.ID
             INTO @InsertedIDs 
             --
    SELECT [Name],[Lname],@Code
    FROM   @tblEmp A
    WHERE  NOT EXISTS (SELECT 1
                       FROM   Employee B
                       inner join Contactdetail c
                       on cid = B.cid
                       WHERE  B.[Name] = A.[Name]
                              AND B.[Lname] = A.[Lname]
                              AND C.[mobno] = A.[mobno]) 

它将id的值存储到另一个表中。我想将id添加到该数据表或另一个表中，其中包含来自datatable的所有记录。如何做到这一点？

It store value of id into another table.I want to add id to that datatable or another table with all record from datatable.How to do that?

推荐答案

读取 - OUTPUT子句（Transact-SQL） [ ^ ]。



转到示例。 甲。使用OUTPUT INTO和一个简单的INSERT语句。



你可以这样做 INSERTED.ColumnName 。只需将所有这些插入的列值插入新表或其他内容。

Read - OUTPUT Clause (Transact-SQL)[^].

Go to "Examples". A. Using OUTPUT INTO with a simple INSERT statement.

You can do like INSERTED.ColumnName. Just insert all these inserted column values to a new Table or something.

在插入之前，获取其最后一个id是当前目标表并分配给变量。然后插入你的记录。插入后，从目的地表中获取所有记录，这些记录大于你指定的变量。



为此'。如果它在Employee表中的组合，请不要在Employee.Else中插入记录（[Name]，[Lname]，@ Code）插入'我更喜欢使用合并语句



查看此内容。

https://msdn.microsoft.com/en-us/library/bb510625.aspx

before insert, get the last id of which is present destination table and assign into a variable. then insert your records..after inserting, fetch all records from destination table which is greater then that variable your assigned.

and for this that '.If combination of it present in Employee table,pls don't insert record([Name], [Lname], @Code ) in Employee.Else Insert' i would prefer to use Merge Statement

check this.
https://msdn.microsoft.com/en-us/library/bb510625.aspx

这篇关于如何在表上多次插入并从表中分配后从表中获取所有id？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！