Insert Identity Column Value into Table from Another Table

Inserting a identity column value into another table

Try the following steps

1) Apply transaction level on insertion

2) Get last inserted id using Scope_Identity() function.

When you apply transaction level it will lock your tables and other/same user cannot insert the value in this time.

try this it will work for you.

Insert identity column value into table from another table?

You can use the output clause. From the documentation (emphasis mine):

The OUTPUT clause returns information from, or expressions based on, each row affected
by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be
returned to the processing application for use in such things as
confirmation messages, archiving, and other such application
requirements. The results can also be inserted into a table or table
variable. Additionally, you can capture the results of an OUTPUT
clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and
insert those results into a target table or view.

like so:

create table #tempids (a int) -- a temp table for holding our identity values

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
values 
('bvju','hjab')

You then asked...

What if the insert is from a select instead?

It works the same way...

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
select -- except you use a select here
 Column1
,Column2
from SomeSource

It works the same way whether you insert from values, a derived table, an execute statement, a dml table source, or default values. If you insert 1000 records, you'll get 1000 ids in #tempids.

How can return identity column value from table and insert other table in SQL Server?

Try this:

-- declare a variable to hold your newly created IDENTITY value
DECLARE @Identity BIGINT

-- insert your values into the first table
INSERT INTO dbo.Table1(list-of-columns)
VALUES(list-of-values);

-- get the newly generated IDENTITY value into your variable
SET @Identity = SCOPE_IDENTITY();

-- use that variable in your second INSERT
INSERT INTO dbo.Table2(table1Id)
VALUES(@Identity)

Update:

With your updated question with the INSERT statement, use this code to include the @Identity value:

INSERT INTO dbo.Interconnect_Traffic_Analysis_MAIN(Code_Op, Name_Op, Shomare_Tel, Duration, table1ID) 
   SELECT 
       t7, t8, t9, t10, @Identity 
   FROM
       Interconnect_Traffic_Analysis_Temp;

---

Related Topics