Adding Multiple Rows to a Table1:50 with Andrew Chalkley
Inserting data into a table doesn't have to be restricted to one row at a time. You can insert multiple rows in a single statement.
Inserting multiple rows in a single statement:
INSERT INTO <table> (<column 1>, <column 2>, ...) VALUES (<value 1>, <value 2>, ...), (<value 1>, <value 2>, ...), (<value 1>, <value 2>, ...);
INSERT INTO users (username, first_name, last_name) VALUES ("chalkers", "Andrew", "Chalkley"), ("ScRiPtKiDdIe", "Kenneth", "Love"); INSERT INTO movies (title, genre, year_released) VALUES ("Starman", "Science Fiction", 1984), ("Moulin Rouge!", "Musical", 2001);
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
When writing queries or reading information from a database, you generally 0:00 issue one query at a time because you want to do something with the result set. 0:04 However, when you're inserting values, especially when you're populating 0:09 an initial data set, you may want to insert multiple rows at once. 0:13 Let's look at adding some more books to our books table. 0:18 Here's three new books we want to add, The Circle, 0:22 Contact, and Animal Farm. 0:28 You tend to see multiple statements like this in files that set up the initial 0:34 state of a database, called database seed files. 0:39 You're seeding information into the database. 0:43 Sometimes, if you're adding a couple of entries yourself, 0:47 you don't want to write the first bit of the query over and over again. 0:50 What you can do is separate each of the sets of values, 0:59 meaning the values in parentheses, with a comma. 1:03 You can use a return in your white space between key words, values, table and 1:19 column names. 1:24 You may want to do this to make your SQL statement clearer to read. 1:25 So, in short, you can insert multiple rows in a single statement. 1:30 You'd write INSERT INTO, the set of columns in the order you want to specify 1:34 your values, the values keyword, then each row's 1:39 values in parentheses, with each set of values separated by a comma. 1:44
You need to sign up for Treehouse in order to download course files.Sign up