0

I have created two tables one named 'A' and one named 'B'. They both share a column called ID.

CREATE TABLE A (
ID int NOT NULL,
random1 char(10),
random2 char(10)
);

CREATE TABLE B (
ID int NOT NULL,
random3 char(10),
random4 char(10)
);

I'm trying to make it so when I INSERT INTO Table A, it copies/inserts itself into Table B:

INSERT INTO A (ID, random1, random2)
VALUES (123, 'Value2', 'Value3');

SELECT * FROM TABLE A should return

| ID | random1 | random2 |
|:---- |:------:| -----:|
| 123  | Value2   | Value3 |

and SELECT * FROM TABLE B should return

| ID | random3 | random4 |
|:---- |:------:| -----:|
| 123  | NULL   | NULL |

I tried to use relationships/Primary-Foreign connections to achieve it but to no avail.

This is probably not the best way of making the table but I have already finished most of the other work and this is one of the last things to do.

NecRaul
  • 1
  • 1
  • The way you have it written, just add a second insert statement with the same Value1? But I think you might have oversimplied your issue. – Dale K Sep 13 '22 at 23:30
  • 1
    Maybe `INSERT INTO A (ID, random1, random2) OUTPUT inserted.ID INTO B (ID) VALUES (123, 'Value2', 'Value3');` – Charlieface Sep 13 '22 at 23:59
  • The is no magic switch to make the happen. You could write a trigger to do it, but I would recommend not doing that as it hides the logic and makes it hard to maintain. Really just use 2 insert statements and provide the id to both of them. I also wonder why you have a relationship like that between 2 tables without it being a proper foreign key? – Dale K Sep 14 '22 at 00:02
  • @Charlieface That worked like a charm, thank you very much sir. I didn't understand the documentation on "inserted" that much and was trying to do `INSERT INTO A (ID, random1, random2) OUTPUT inserted.ID INTO B (ID) VALUES (123);` – NecRaul Sep 14 '22 at 00:04
  • 1
    Note that that only works if `B` is not part of a FK relationship and has no check constraints. Otherwise you need separate `INSERT` statements, or a trigger. – Charlieface Sep 14 '22 at 00:06

0 Answers0