How to insert data into two tabels?

how to insert data into two tables simultaneously
how to insert data into multiple tables using single query in sql server
mysql insert into multiple tables with foreign keys
how to insert data into multiple tables using single query in mysql
insert into multiple tables sql
stored procedure to insert two tables with relationship
sql insert into multiple tables from select
insert into two tables at once postgres

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();
    }
}

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();

How to insert into two tables using a single MySQL query?, Here is the SQL script that inserts data into the 3 tables as expected. Declare @ StudentName Duration: 14:52 Posted: Feb 6, 2017 Stored procedure to insert data into multiple tables in sql server Sanjay Yadav 2018-07-11T23:26:00-07:00 5.0 stars based on 35 reviews Inserting into multiple tables: Introduction: In this article I am going to explain how to insert data into multiple tables . For this

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

SQL script to insert into many to many table, multipleinsert #storedprocedure #sqlserver query to insert values into multiple tables using Duration: 7:26 Posted: Oct 1, 2018 I have this code with which I can scrape the profiles of google scholar and i can insert just fine into the table "Autor" all the data I want, but when I want to insert the "idpaper&

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.

sql query insert multiple values into multiple tables sql server 2012 , let me explain suppose i have two tables 'abc' and 'xyz' First of all i have created if you want insert data into two table then repeat your insert� This is a example of one form data insert for a multiple tables. How to insert data into two different tables using PHP and MYSQL. Related. 1219.

[Solved] insert query to insert into two table at once, You can not insert data into 2 tables simultaneously in a single session. But if u split the insert statements into 2 statements, it is going to give� Hi nav33n. Thank you for your quick reply to my post, but I am not sure which one to use. I will explain what I am using. I have created a PHP called it add_booking_record.php which have a form to get the user input and once user submit the form my add_booking.php page will be used to open the connection with the Mysql database and insert the data into table booking using the insert query but

INSERT INTO MULTIPLE TABLES AT ONCE – SQLServerCentral, adr_information (idref, info) SELECT id, 'test data' FROM ins;. which works perfectly for exactly two tables. Main problem here is, that all additional� Another option is to run the two inserts separately, leaving the FK column null, then running an update to poulate it correctly. If there is nothing natural stored within the two tables that match from one record to another (likely) then create a temporary GUID column and populate this in your data and insert to both fields.

Insert into multiple tables with one query, Note that this answer assumes that the two tables sustain a data relationship. Otherwise, wrapping two separate INSERT statements in a transaction would be � To add multiple rows to a table at once, you use the following form of the INSERT statement: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), (value_list_n); In this syntax, instead of using a single list of values, you use multiple comma-separated lists of values for insertion.

Comments
  • I don't understand what your purpose is. Which two labels? I see only ID and Film_Id in Record table.
  • And why hold the same value in two different tables?
  • @ErayBalkanli - It seems he wants to fill both tables film and record at the same time with the foreign key relation. So his question is basically How to insert a record with foreign relations? - at least thats how I understand it :)
  • Just add a second insert into the Record Table.
  • @jdweng but what with the foreign key, so how does he fill the id of the table film into the related table record's column film_id
  • Out of curiosity, why not ExecuteScalar(); instead of ExecuteNonQuery();?
  • @RandRandom: I am not sure, just my preference. There is also tiny nuance. ExecuteScalar returns the first column of the first row. So I expect we shall user 'SELECT LAST_INSERT_ID()' instead of return LAST_INSERT_ID();.
  • Its been so long since I last actually wrote SQL my self (nonetheless mysql), so I don't really know myself. :) - ORMs drained my SQL knowledge faster than I want to admit. :)