Inserting a new record into 2 tables at the same time
-
I took Jorgen's advice and modified my Movie database I split the table into 2, the first table being movie information and the 2nd table being FLV information So I can do the JOINs in displaying the data, and using it to create a Flash Movie Player, But it just dawned on me that I have no clue on how to insert and update records for my new setup here. So I came up with this. My question, am I on the right track here, Inserting the first table, getting the ID of the record so I can use the ID in the 2nd table? Or is there a better way in which I can insert in one shot? I'm not ready to write stored procedures yet, in case that is suggested.
INSERT INTO MovieInfo(
MovieName, MovieType, MoviePath, MoviePostage, MovieThumbnail, flv, h264
)
VALUES(
@MovieName, @MovieType, @MoviePath, @MoviePostage, @MovieThumbnail, @flv, @H264
);
DECLARE @mID AS INT;
SET @mID = (
SELECT MovieID FROM MovieInfo WHERE MovieName=@MovieName
)
INSERT INTO MovieInfo_flv(
movieID, flv_movieName, flv_skin, flv_folderpath, flv_filename
)
VALUES(
@mID, @flv_movieName, @flv_skin, @flv_folderpath, @flv_filename
) -
I took Jorgen's advice and modified my Movie database I split the table into 2, the first table being movie information and the 2nd table being FLV information So I can do the JOINs in displaying the data, and using it to create a Flash Movie Player, But it just dawned on me that I have no clue on how to insert and update records for my new setup here. So I came up with this. My question, am I on the right track here, Inserting the first table, getting the ID of the record so I can use the ID in the 2nd table? Or is there a better way in which I can insert in one shot? I'm not ready to write stored procedures yet, in case that is suggested.
INSERT INTO MovieInfo(
MovieName, MovieType, MoviePath, MoviePostage, MovieThumbnail, flv, h264
)
VALUES(
@MovieName, @MovieType, @MoviePath, @MoviePostage, @MovieThumbnail, @flv, @H264
);
DECLARE @mID AS INT;
SET @mID = (
SELECT MovieID FROM MovieInfo WHERE MovieName=@MovieName
)
INSERT INTO MovieInfo_flv(
movieID, flv_movieName, flv_skin, flv_folderpath, flv_filename
)
VALUES(
@mID, @flv_movieName, @flv_skin, @flv_folderpath, @flv_filename
)You either do this as a 2 statement operation or you create a stored procedure - the SP path is by far the simplest an most robust. I presume you have an IDENTITY field the Movie table. So when a record is inserted the system puts the new ID into a variable called @Scope_Identity you can get the value from there. I used stored procs for EVERYTHING, the UI/Service NEVER talks directly to a table/view, so I have a code generator to build my CRUD procs. So I am not going to be able to help with string inserts :-O
Never underestimate the power of human stupidity RAH
-
You either do this as a 2 statement operation or you create a stored procedure - the SP path is by far the simplest an most robust. I presume you have an IDENTITY field the Movie table. So when a record is inserted the system puts the new ID into a variable called @Scope_Identity you can get the value from there. I used stored procs for EVERYTHING, the UI/Service NEVER talks directly to a table/view, so I have a code generator to build my CRUD procs. So I am not going to be able to help with string inserts :-O
Never underestimate the power of human stupidity RAH
-
Well thanks for the confirmation on that. I guess I'll take a stab on stored procedures then.
So here is some pseudo code for that. Declare incoming variables Insert into student table Get Scope_Identity into a variable Check for existing course - this may be done in the client Insert missing course Get Scope_Identity into another variable Insert variables into link table (this is a many to many table linking the course and the student records) Return the student record - I do this so the client has all the information about the student including the ID value.
Never underestimate the power of human stupidity RAH
-
So here is some pseudo code for that. Declare incoming variables Insert into student table Get Scope_Identity into a variable Check for existing course - this may be done in the client Insert missing course Get Scope_Identity into another variable Insert variables into link table (this is a many to many table linking the course and the student records) Return the student record - I do this so the client has all the information about the student including the ID value.
Never underestimate the power of human stupidity RAH