{"id":6067,"date":"2018-06-19T14:06:00","date_gmt":"2018-06-19T14:06:00","guid":{"rendered":"https:\/\/www.jquery-az.com\/?p=6067"},"modified":"2018-06-19T14:06:00","modified_gmt":"2018-06-19T14:06:00","slug":"insert-multiple-rows-sql","status":"publish","type":"post","link":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/","title":{"rendered":"How to insert multiple rows in SQL?"},"content":{"rendered":"

In the INSERT INTO statement<\/a> tutorial, we have seen how to enter new records into the database tables. You may use column names<\/strong> with VALUES or without using the column names for inserting records.<\/p>\n

In many scenarios, you may require entering multiple rows by using the single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.<\/p>\n

First way \u2013 Separating VALUES part by a comma<\/strong><\/h3>\n

As using simple INSERT INTO statement, we used this statement for entering single record (for our example employee table):<\/p>\n

INSERT INTO sto_employees\r\n\r\n(id, emp_name, emp_salary, emp_age, join_date)\r\n\r\nVALUES\r\n\r\n(13, 'Shaan', 5000.00, 35, '2016-12-13');<\/pre>\n
For entering multiple records just separate the VALUES by commas. See the example below:<\/p>\n
INSERT INTO sto_employees\r\n\r\n(id, emp_name, emp_salary, emp_age, join_date)\r\n\r\nVALUES\r\n\r\n(14, 'Jumma', 4500, 35, '2017-09-15'),\r\n\r\n(15, 'Hareem', 3600, 26, '2018-05-29'),\r\n\r\n(16, 'Rudaba', 3200, 24, '2016-11-04');<\/pre>\n
The above query will enter multiple records into the sto_employees<\/em> table. You can see, after the VALUES keyword, the first row values are enclosed in parenthesis. This is followed by a comma and then second row values and so on.<\/p>\n
This single query should enter records in MS SQL Server as well as MySQL database table.<\/p>\n
Insert multiple records without specifying the column names<\/strong><\/h3>\n
Multiple records can also be entered without providing the column names (just like in the INSERT INTO command with single record).<\/p>\n
In that case, you have to provide all column name values in the same sequence as in the actual physical table. Otherwise, an error may be raised if fewer values are provided than the number of columns in the table or if data type mismatch.<\/p>\n
The following query inserts three records in the employee’s table without using the column names:<\/p>\n
INSERT INTO sto_employees\r\n\r\n\r\n\r\nVALUES\r\n\r\n(17, 'Mike', 4500, 35, '2017-09-15', '012-1454-457474'),\r\n\r\n(18, 'Jameel', 3600, 26, '2018-05-29', '005-5774-457474'),\r\n\r\n(19, 'Janaan', 3200, 24, '2016-11-04', '001-4747-457474');<\/pre>\n
Again, this query is tested on MS SQL Server as well as MySQL database.<\/strong><\/p>\n
Second Way \u2013 Using INSERT INTO & SELECT statements<\/strong><\/h3>\n
The combination of INSERT INTO and SELECT statements also allows you entering multiple records by single statement. However, this is used to copy data from one table to another.<\/p>\n
The source table (from where you are copying data) is specified in the SELECT statement. The target table (where you are inserting records) is specified in the INSERT INTO command.<\/p>\n
The following example shows an example query:<\/p>\n
INSERT INTO employees_sub\r\n\r\nSELECT * FROM sto_employees;<\/pre>\n
In that case, the complete table rows of sto_employees will be inserted into the employee_sub table. The columns sequence and specification must be the same (names can be different).<\/p>\n
You may also use WHERE clause<\/a> in SELECT statement for inserting specific rows. I have written a detailed guide of using this option in INSERT INTO with SELECT<\/a> for copying data.<\/p>\n
Third way: The UNION Operator<\/strong><\/h3>\n
In the third way of inserting multiple rows by\u00a0single query is using the UNION operator. The UNION operator is basically used to combine the results of SELECT statements.<\/p>\n
So, again this technique uses INSERT INTO and SELECT statement, however rather than copying the data from one table to another, you may insert data with independent values (just like the first way).<\/p>\n
The INSERT INTO specifies the target table while in the SELECT statements, values are specified. Multiple SELECT statements are combined by the UNION operator as shown in the query below:<\/p>\n
INSERT INTO [sto_employees]\r\n\r\n\u00a0([id]\r\n\r\n\u00a0,[emp_name]\r\n\r\n\u00a0,[emp_age]\r\n\r\n\u00a0,[emp_salary]\r\n\r\n\u00a0,[join_date]\r\n\r\n\u00a0,[phone])\r\n\r\nSELECT 20, 'Tim', 45, 5500, '2017-09-15','044-4745-145745'\r\n\r\nUNION ALL\r\n\r\nSELECT 21, 'Tony', 47, 6000, '2017-09-15','092-1545-474184'\r\n\r\nUNION ALL\r\n\r\nSELECT 22, 'Tania', 34, 4500, '2017-09-15','056-2455-454745';<\/pre>\n
The result-set before and after executing this query (for demo only):<\/strong><\/p>\n
\"SQL<\/p>\n
Note: <\/strong>This query is tested on MS SQL Server database.<\/p>\n","protected":false},"excerpt":{"rendered":"
In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.<\/p>\n","protected":false},"author":1,"featured_media":6068,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[22],"tags":[],"yoast_head":"\n3 Ways to Insert Multiple Rows in SQL<\/title>\n<meta name=\"description\" content=\"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"3 Ways to Insert Multiple Rows in SQL\" \/>\n<meta property=\"og:description\" content=\"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-19T14:06:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/05\/44-1-SQL-Insert-multiple.png\" \/>\n\t<meta property=\"og:image:width\" content=\"615\" \/>\n\t<meta property=\"og:image:height\" content=\"518\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"jqueryaz\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"jqueryaz\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/\",\"url\":\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/\",\"name\":\"3 Ways to Insert Multiple Rows in SQL\",\"isPartOf\":{\"@id\":\"https:\/\/www.jquery-az.com\/#website\"},\"datePublished\":\"2018-06-19T14:06:00+00:00\",\"dateModified\":\"2018-06-19T14:06:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.jquery-az.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3\"},\"description\":\"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.jquery-az.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to insert multiple rows in SQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.jquery-az.com\/#website\",\"url\":\"https:\/\/www.jquery-az.com\/\",\"name\":\"\",\"description\":\"PHP, Bootstrap, jQuery, CSS, Python, Java and others\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.jquery-az.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.jquery-az.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3\",\"name\":\"jqueryaz\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.jquery-az.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e2a3743b4da71bd52984e02bd5c3280c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e2a3743b4da71bd52984e02bd5c3280c?s=96&d=mm&r=g\",\"caption\":\"jqueryaz\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"3 Ways to Insert Multiple Rows in SQL","description":"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/","og_locale":"en_US","og_type":"article","og_title":"3 Ways to Insert Multiple Rows in SQL","og_description":"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.","og_url":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/","article_published_time":"2018-06-19T14:06:00+00:00","og_image":[{"width":615,"height":518,"url":"https:\/\/www.jquery-az.com\/wp-content\/uploads\/2018\/05\/44-1-SQL-Insert-multiple.png","type":"image\/png"}],"author":"jqueryaz","twitter_misc":{"Written by":"jqueryaz","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/","url":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/","name":"3 Ways to Insert Multiple Rows in SQL","isPartOf":{"@id":"https:\/\/www.jquery-az.com\/#website"},"datePublished":"2018-06-19T14:06:00+00:00","dateModified":"2018-06-19T14:06:00+00:00","author":{"@id":"https:\/\/www.jquery-az.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3"},"description":"In many scenarios, you may require entering multiple rows by using single query of SQL. In this tutorial, I will show you how to enter two or more records by INSERT INTO statement.","breadcrumb":{"@id":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.jquery-az.com\/insert-multiple-rows-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.jquery-az.com\/"},{"@type":"ListItem","position":2,"name":"How to insert multiple rows in SQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.jquery-az.com\/#website","url":"https:\/\/www.jquery-az.com\/","name":"","description":"PHP, Bootstrap, jQuery, CSS, Python, Java and others","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.jquery-az.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.jquery-az.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3","name":"jqueryaz","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.jquery-az.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e2a3743b4da71bd52984e02bd5c3280c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e2a3743b4da71bd52984e02bd5c3280c?s=96&d=mm&r=g","caption":"jqueryaz"}}]}},"_links":{"self":[{"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/posts\/6067"}],"collection":[{"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/comments?post=6067"}],"version-history":[{"count":0,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/posts\/6067\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/media\/6068"}],"wp:attachment":[{"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/media?parent=6067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/categories?post=6067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jquery-az.com\/wp-json\/wp\/v2\/tags?post=6067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}