How to insert data into two tabels?

48
February 12, 2019, at 1:30 PM

I have two tables FILM(Id, Title, Director, Year, Category) and Record(Id, Film_id)

Id from FILM table is used as a foreign key as Film_id in Record table.

I want to insert data at the same time date into both tables. How construct a sql query with INSERT?

using (var connection = new MySqlConnection(constring))
{
    connection.Open();
    using (var cmdDataBase = connection.CreateCommand())
    {   
        cmdDataBase.CommandText = "INSERT INTO film(Title, Director, Year, Category) VALUES (@Title, @Director, @Year, @Category) ";
        cmdDataBase.Parameters.Add(new MySqlParameter("@Title", fm.Title));
        cmdDataBase.Parameters.Add(new MySqlParameter("@Director", fm.Director));
        cmdDataBase.Parameters.Add(new MySqlParameter("@Year", fm.Year));
        cmdDataBase.Parameters.Add(new MySqlParameter("@Category",n));
        cmdDataBase.ExecuteNonQuery();
    }
}
Answer 1

You get the last auto-generated ID with LAST_INSERT_ID(). Then, you'd use a transaction, because you don't want to insert a parent record, if you can't also insert the child record, or so I understand.

This is it more or less. You should add error handling (try, catch) of course.

// Start transaction
MySqlTransaction transaction = connection.BeginTransaction();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.Transaction = transaction;
// Parent record
command.CommandText = "INSERT INTO film(Title, Director, Year, Category) VALUES (@Title, @Director, @Year, @Category) ";
command.Parameters.Add(new MySqlParameter("@Title", fm.Title));
command.Parameters.Add(new MySqlParameter("@Director", fm.Director));
command.Parameters.Add(new MySqlParameter("@Year", fm.Year));
command.Parameters.Add(new MySqlParameter("@Category",n));
command.ExecuteNonQuery();
// Child record        
command.CommandText = "INSERT INTO record (Film_id) VALUES (LAST_INSERT_ID())";
command.ExecuteNonQuery();
// Commit inserts
transaction.Commit();
Answer 2

You need to do it in sequence. First, add record to your main table Film, then other table Record. Check below:

INSERT INTO Film(Title, Director, Year, Category)
VALUES('Title', 'DirectorName', 1990, 'Action');
--Assuming ID for Record table is AUTO_INCREMENT column
INSERT INTO Record(Film_id)
VALUES ((SELECT id FROM Film WHERE Title='Title' and Director='Director'));

Also, LAST_INSERT_ID() can be used:

INSERT INTO Film
...
INSERT INTO Record
...
film_id = LAST_INSERT_ID()

Ref: Mysql: How to insert values in a table which has a foreign key

Answer 3

If you are using MySQL try to add ;return LAST_INSERT_ID(); at the end of you query and add return parameter as below.

var returnVal = cmdDataBase.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnVal.Direction = ParameterDirection.ReturnValue;

After cmdDataBase.ExecuteNonQuery(); extract id from returnVal

var id = returnParameter.Value;

After that you can use id to perform the second insert.

READ ALSO
Connect to node.js after installing MySQL

Connect to node.js after installing MySQL

When I type var mysql=require('mysql');, I get an error that mysql module is not found

57
(2059,“Authentication Plugin 'caching_sha2_password'”) when running server connected with MYSQL database on Django

(2059,“Authentication Plugin 'caching_sha2_password'”) when running server connected with MYSQL database on Django

I want to configure my django project in order to connect it with database in MYSQL I created with workbench 80, and then I want to run the server by running

26
Is there a way to use the values of a column as headers?

Is there a way to use the values of a column as headers?

I'm trying to make a table that shows all students and their grades by courseSo far I have something like this

30