1

From a select, I try to insert data into two tables :

DECLARE @ORIGIN TABLE (ID INT IDENTITY, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), STREET VARCHAR(50))
DECLARE @PERSON TABLE (ID INT IDENTITY, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50))
DECLARE @ADDRESS TABLE (ID INT IDENTITY, ID_PERSON INT, STREET VARCHAR(50))

INSERT INTO @ORIGIN 
VALUES ('Carl', 'MAX', '1 All Road'),
       ('Elsa', 'SCARLET', 'On the tail')

I want something like :

INSERT INTO @PERSON (FIRST_NAME, LAST_NAME)
OUTPUT inserted.ID, o.STREET INTO @ADDRESS (ID_PERSON, STREET)
    SELECT FIRST_NAME, LAST_NAME 
    FROM @ORIGIN AS o

The OUTPUT doesn't have the same columns as the SELECT:

The multi-part identifier "o.STREET" could not be bound.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vernou
  • 6,818
  • 5
  • 30
  • 58
  • Your fields don't align; the `o.STREET` is not part of the `SELECT` statement and should be `inserted.STREET` if it were included. Also `@PERSON` does not contain the field `STREET` – Peter Smith Jul 29 '19 at 15:29
  • 1
    Why does it have to be a single query? If I had this task, I'd do it with a stored procedure. – Brian Jul 29 '19 at 15:31
  • @Perter : Of course, but if I can't add STREET in the SELECT because STREET isn't in '@PERSON' table. I need transfert STREET from '@ORIGIN' directly in '@ADDRESS'. – vernou Jul 29 '19 at 15:33
  • @Brian : For the beauty. Actualy, I have made a stored procedure to continue my work. – vernou Jul 29 '19 at 15:34
  • And you will need to do that as a separate `INSERT` statement; you can;t do it like this. – Peter Smith Jul 29 '19 at 15:35
  • @Perter : The second INSERT ('@ADDRESS') don't know the id generate in first insert ('@PERSON'). But if you can, two inserts is a valid answer. – vernou Jul 29 '19 at 15:39
  • @marc_s : Of course, but I hope it has a trick. – vernou Jul 29 '19 at 15:40
  • 2
    Sorry, @marc_s, but you are wrong on this one. There is a trick - see dupe for details. – Zohar Peled Jul 29 '19 at 15:59
  • 1
    @ZoharPeled: interesting - thanks for the link - I wasn't aware of that approach; you learn something new every day! – marc_s Jul 29 '19 at 16:38
  • 1
    @ZoharPeled: The merge solution is short and fast, perfect. For 200k Rows, 6 minutes with cursor and 20 secondes with merge. Thank. – vernou Jul 30 '19 at 07:05
  • Glad to help ;-) – Zohar Peled Jul 30 '19 at 10:34

0 Answers0