0

I'm trying to insert into a table that is joined in my c# program, I'm having an issue with the below SQL statement on SQL Server:

INSERT INTO DartBox (DartBoxNumber, ReturnDate, Comments, SerialSDD)
VALUES (1, 2, 3, 4)

SELECT d.DartBoxNumber, d.ReturnDate, d.Comments, s.SerialSDD
FROM DartBox d 
LEFT JOIN DartBoxSerials s ON d.DartBoxID = s.DartBoxReturnID

I want to insert data into two tables at once, there is a relationship between DartBox and DartBoxSerials (there can be many serials in the dartbox)

When I try and run the query in SQL I get:

Invalid column name 'SerialSDD'.

It does exist in the joined output though.

Any assistance would be great?

This worked using the link below and this is how to do it: (I've removed the date bit due to a type mismatch)

BEGIN TRANSACTION
DECLARE @DataID int;
INSERT INTO DartBox (DartBoxNumber, Comments)
VALUES (1, 3)
SELECT @DataID = scope_identity();
INSERT INTO DartBoxSerials (DartBoxReturnID, SerialSDD)
VALUES (@DataID, 4);
COMMIT
Andy00001
  • 87
  • 2
  • 9
  • You will have to separate the insert statements. You will not be able to do it this way. – briskovich Apr 17 '18 at 15:54
  • I have ID's for each form that increment in each table that I need to link together - how can I do this? I need the serials to tie to the box and be able to enter them at the same time. Cheers – Andy00001 Apr 17 '18 at 15:56
  • I bet the SerialSDD field is missing in table DartBox – Daniel Marcus Apr 17 '18 at 16:24
  • Thank you! Been looking all afternoon how to do this – Andy00001 Apr 17 '18 at 16:44
  • @DanielMarcus yes it is in dartboxserials that’s why I’m looking to insert into the joined table but I think from other responses it’s not possible – Andy00001 Apr 17 '18 at 17:01
  • Yes you need to do it separately - sorry misunderstood – Daniel Marcus Apr 17 '18 at 17:13
  • @DanielMarcus oh ok, how can I do it separately and have the same unique identifier on both? I want to join these tables later to show all the serials in a particular crate. In this example I’m only doing one serial to get it working but after I will be adding many. – Andy00001 Apr 17 '18 at 17:21
  • Capture scope_identity() on the insert to the first table into a variable - this will be the value of the last identity value created when you do the insert - then insert that same value to your second table so you can join them later – Daniel Marcus Apr 17 '18 at 17:27
  • @DanielMarcus thanks so much! I’ll try this tonight – Andy00001 Apr 17 '18 at 17:42

0 Answers0