0

Let's say that i have two relational tables : table A and table B.

table B have a foreign key of table A , so in order to insert a row in B we need first to insert a row in A. so i have inserted the first row in A :

insert into A (c1,c2) values(v1,v2)

What i want is to get the id of the inserted row to use it as a foreign key when i insert into the table b . how to do that ?

while searching for a solution i found here , that i need to declare a new table:

declare @KEY table(KEY int)

Insert into A (c1,c2)
Output inserted.Id into @kEY
values(v1,v2)

but when i tried to use it :

 Insert into B (C1,C2) values(@KEY,v2)

I get an error telling the @KEY is not declared !

so any idea ?

A.HADDAD
  • 1,809
  • 4
  • 26
  • 51

1 Answers1

2

A table variable is not the same thing as a scalar variable. You can't use the values clause like this with a table variable - you need to select from it:

declare @KEY table(KEY int)

Insert into A (c1,c2)
Output inserted.Id into @kEY
values(v1,v2)

Insert into B (C1) 
Select [Key]
From @Key;
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • ok how to do that into my values clause ? i can't do this values(select [key] from@key), should i create a select before an put it into a local variable ? – A.HADDAD Dec 16 '19 at 11:02
  • 1
    You don't do that into your values clause, the `select` is instead of the `values` clause. If you need extra data you can do `select [Key], 'HardCodedString', @Variable FROM @Key` – Zohar Peled Dec 16 '19 at 11:06
  • what if i have other columnes , how they get their values, i think the solution is to get the select out ,pass the result in a variable and then insert it in values – A.HADDAD Dec 16 '19 at 11:43
  • 1
    In an insert statement, you can output anything from the `inserted` pseudo table to you table variable (providing it has the relevant columns) – Zohar Peled Dec 16 '19 at 11:44