0

I have table A and table B with many-to-many relationship. Table AB is a linking table.

Table:  Columns:
A       { Id, Name }
AB      { A_Id, B_Id }
B       { Id, Name }

I want to insert row into table A and then use that row's Id (Id has autoincremented identity) to insert into linking table.

Is it possible to do it in single SQL command?

Example (pseudo-sql): 

insert into A (Name) values (@name)
insert into AB (A_Id, B_Id) values ([previous id], @b_Id)
Tschareck
  • 4,071
  • 9
  • 47
  • 74

3 Answers3

5

In SQL Server, the safest way to do this is to use the output clause, which is explained here:

declare @ids table (id int);

insert into A (Name)
    output inserted.id into @ids
    values (@name);

insert into AB (A_Id, B_Id) 
    select i.id, @b_Id
    from @ids i;

Why is output safer? First, it does not rely on transaction or session semantics. It simply captures the data that is actually being inserted and makes it available to subsequent statements.

Second, it can handle multiple rows being inserted (although that is not an issue in this case).

Third, it can return other columns apart from the ids, which can be handy.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

you can use @@IDENTITY which returns the last inserted identity , see https://learn.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql.

Gavin
  • 491
  • 3
  • 5
  • I don't like this answer because that is dangerous. What returns identity? the really last insert on that table? what happens if you insert two or more rows in the same time. what happens a trigger inserts in background an other row into an other table? – coding Bott Apr 05 '18 at 15:15
  • I agree with @BerndOtt, but on the off chance someone is truly working with single record insertion, the `scope_identity()` function would actually be relatively safe (at least WAY safer) as long as there aren't background triggers doing crazy things. – Brian Jorden Apr 05 '18 at 16:06
  • yes it's better but scope_identity still does not work, when you insert multiple rows. – coding Bott Apr 06 '18 at 08:20
-2

Yes:

INSERT INTO A (Name) VALUES (@name)

INSERT INTO AB (A_Id, B_Id)
SELECT A.Id, @B_Id
FROM A
WHERE A.Name = @name
ORDER BY A.Id

Update: This solution is based on knowing what the constraints need to be from Table A as you insert into table AB. In the event that there are pre-existing values of @name in table A (i.e., duplicates), then you will may need to consider adding additional constraints. It would be difficult to know without knowing the data model. Though, I suspect you are working with dimension tables, which implies that there won't be duplicate values because it is expected that you would already have a mapping for it.

That also brings up a good point; make sure you have a primary key on the mapping table that contains the foreign key columns for tables A and B. In addition, create a unique index on the Name column in table A. Duplicates should not be allowed as that will break the mapping table.

J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • 1
    Wrong: this links **all** A records to B_Id – Peter B Apr 05 '18 at 14:42
  • @PeterB I have updated my answer. – J Weezy Apr 05 '18 at 15:03
  • As you say there is no guarantee that Name is unique (say table A = Customer and @name = 'John Smith'). Also Name could have no index making it slow. All this can be avoided by using `output` which exists since SQL 2008. – Peter B Apr 05 '18 at 15:37
  • @PeterB this is unlikely due to the use of a mapping table. If the Name value to be inserted already exists then the many-to-many mapping would be broken - there must be unique records on the source mapping tables. In this case, A.Name should have a unique index on it. So, both your solution and my solution will work. It boils down to personal preference. – J Weezy Apr 05 '18 at 15:47