How can we insert record in two tables in same stored Procedure in SQL Server. I need just inserted ID field from first table to insert it as reference to second table. As in multi user environment we will have concurrent inserts.
Asked
Active
Viewed 262 times
0
-
1Take a look at `scope_identity()` function. – Arvo Mar 11 '16 at 13:39
-
You also can use output clause which can work with bulk inserts as well – TheGameiswar Mar 11 '16 at 13:53
-
1Possible duplicate of [Inserting to one table, insert the ID to second table](http://stackoverflow.com/questions/23760439/inserting-to-one-table-insert-the-id-to-second-table) – Tab Alleman Mar 11 '16 at 14:03
2 Answers
3
BEGIN TRAN
DECLARE @id INT
INSERT INTO tbl1
VALUES (..)
SET @id = SCOPE_IDENTITY()
INSERT INTO tbl2
VALUES (@id)
COMMIT TRAN
Devart
- 119,203
- 23
- 166
- 186
1
begin tran
Declare @tbl table (id int)
insert into t11
output inserted.* into @tbl
select 1
insert into t2
select * from @tbl
commit
TheGameiswar
- 27,855
- 8
- 56
- 94