Insert a row which contains foreign key that is auto incremented field in Microsoft SQL using JDBC

Question

I have two tables foo and bar, foo has an auto incremented field colA. I need to insert a record in foo and get the auto incremented value then insert into bar(foreign key). With postgresql it was a simple single query, but where as in SQL server I am not able to find right query.

Postgresql

With row as (insert into foo(colB) values('x') RETURNING colA ) insert into bar(colA) select colA from row

Is there any way to achieve in MsSql using single query?

Server version details

DBMS: Microsoft SQL Server (ver. 13.00.5882)
Driver: Microsoft JDBC Driver 8.2 for SQL Server (ver. 8.2.2.0, JDBC4.2)

Answer 1

Due to limitations of SQL Server you will need to use two insert statements to do this. The following code, for example:

create table dbo.foo (
  colA int not null identity,
  colB varchar(10),
  constraint PK_foo primary key (colA)
);

create table dbo.bar(
  colC int not null,
  constraint FK_bar_colC_foo_colA foreign key (colC) references dbo.foo (colA)
);

insert dbo.foo (colB)
  output inserted.colA into dbo.bar (colC)
  values ('x');

Generates the error message:

Msg 332 Level 16 State 1 Line 12
The target table 'dbo.bar' of the OUTPUT INTO clause cannot be on either side
of a (primary key, foreign key) relationship.
Found reference constraint 'FK_bar_colC_foo_colA'.

But if you comment out the foreign key relation this code works successfully:

create table dbo.foo (
  colA int not null identity,
  colB varchar(10),
  constraint PK_foo primary key (colA)
);

create table dbo.bar(
  colC int not null,
  --constraint FK_bar_colC_foo_colA foreign key (colC) references dbo.foo (colA)
);

insert dbo.foo (colB)
  output inserted.colA into dbo.bar (colC)
  values ('x');

select * from dbo.foo;
select * from dbo.bar;

colA  colB
----- -----
    1 x

colC
-----
    1