{"id":9355,"date":"2022-06-16T06:41:00","date_gmt":"2022-06-16T04:41:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=9355"},"modified":"2026-10-13T13:19:28","modified_gmt":"2026-10-13T11:19:28","slug":"sql-server-insert-into-from-a-select","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-insert-into-from-a-select\/","title":{"rendered":"SQL Server Insert Into from a SELECT with 3 easy queries"},"content":{"rendered":"\n<p class=\"has-text-align-center\"><strong><em>With SQL Server, how to insert data into a table directly from a Select query ?<\/em><\/strong><\/p>\n\n\n\n<p>With the MS database SQL Server, Insert Into from a SELECT query is possible using multiple ways. Here we&#8217;ll cover 3 solutions.<\/p>\n\n\n\n<p>The first solution is to insert directly the data with a SELECT query just after the INSERT INTO keyword. Then, the second solution is to use a SELECT with a UNION ALL to group several rows into a single insert statement. And last but not least, the third one uses a SELECT that retrieves data from another table.<\/p>\n\n\n\n<p>In this article with different example query, we are using the same source table.<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Contents<\/h2><nav><ul><li class=\"\"><a href=\"#h-create-the-sample-sql-table-before-the-insert-into-select\">Create the sample SQL table before the INSERT INTO SELECT<\/a><ul><li class=\"\"><a href=\"#h-solution-1-use-an-insert-into-select-type-query-multiple-times\">Delete the Customers if it exists in your database.<\/a><\/li><li class=\"\"><a href=\"#h-solution-2-sql-server-insert-into-with-multiple-select-and-union-all\">Create the Customers table using this code<\/a><\/li><\/ul><\/li><li class=\"\"><a href=\"#h-solution-3-sql-server-insert-into-from-another-table-s-select\">Solution 1 &#8211; Use a multiple INSERT INTO SELECT in SQL Server<\/a><\/li><li class=\"\"><a href=\"#h-conclusion-on-simple-solutions-to-insert-data-from-selects\">Solution 2 &#8211; Use INSERT INTO SELECTs with UNION ALL in SQL Server<\/a><ul><li class=\"\"><a href=\"#empty-the-table-using-a-truncate-table-first\">Empty the table using a TRUNCATE TABLE first.<\/a><\/li><\/ul><\/li><li class=\"\"><a href=\"#solution-2-sql-server-insert-into-from-a-select-from-the-same-table\">Solution 2 &#8211; SQL Server Insert Into from a Select from the same table<\/a><\/li><li class=\"\"><a href=\"#h-conclusion-on-simple-solutions-to-insert-data-from-selects\">Conclusion on how to use the SQL Server SELECT INTO Query to insert data<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-create-the-sample-sql-table-before-the-insert-into-select\">Create the sample SQL table before the INSERT INTO SELECT<\/h2>\n\n\n\n<p>The very first step, of course, is to create the sample table. To go aheaed, just copy and paste the script into <a href=\"https:\/\/expert-only.com\/en\/ssms\/download-ssms-18\/\">SQL Server Management Studio<\/a>. Of course you will have to adapt the queries to your specific needs before running them on your environement.<\/p>\n\n\n\n<p>First simply reuse the logic and the example that suits you the most. To create the empty Customers table, run the queries below which is the target table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong><em>Delete the Customers if it exists in your database.<\/em><\/strong><\/h3>\n\n\n\n<p><em>Use a development or sandbox database of course!<\/em><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IF EXISTS( \n\tSELECT 1 FROM sys.objects \n\tWHERE object_id = object_id(N'[dbo].[Customers]') \n\t\tAND type in (N'U') )\nDROP TABLE [dbo].[Customers];\nGO\n<\/pre>\n\n\n\n<p>For further examples of <a href=\"https:\/\/expert-only.com\/en\/t-sql\/insert-data-from-a-sql-server-procedure\/\">SQL Server insert using a procedure this time, click here<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"undefined-1\"><strong><em>Create the Customers table using this code<\/em><\/strong><\/h3>\n\n\n\n<p>The Id_Customer is auto incremented to have a rolling list of customer numbers. And the Name column is declared as unique, it means that you cannot enter 2 times the same name in this column.<\/p>\n\n\n\n<p>In other words, it is not possible to have duplicates on the Customers Name column.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Create the Customers table with CustomerID column declared as NOT NULL\nCREATE TABLE dbo.Customers (\n\t[CustomerID]   INT NOT NULL,\n\t[FirstName]    NVARCHAR(20),\n\t[LastName]     NVARCHAR(20) NULL,\n\t[City]         NVARCHAR(20) NULL,\n\t[Country]      NVARCHAR(50) NULL,\n\tCONSTRAINT [CustomersPrimaryKeyCustomerID] \n\t\tPRIMARY KEY CLUSTERED ([CustomerID] ASC);\nGO <\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-solution-1-use-an-insert-into-select-type-query-multiple-times\">Solution 1 &#8211; Use a multiple INSERT INTO SELECT in SQL Server<\/h2>\n\n\n\n<p>The first DML (Data Manipulation Language) solution is the simplest and most common one to implement. Especially if you are familiar with editing several rows in columns, with Excel for example. And for limited number of lines. other wise you will need to genarate multiple independant insert statements.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Insert 4 different lines\nINSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) \nVALUES ( 1, 'Ali','Ahmed','Cairo','Egypt');\nINSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) \nVALUES ( 2, 'Johnny','John','Toronto','Canada');\nINSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) \nVALUES ( 3, 'Johnny','Doe','Mexico City','Mexico');\nINSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) \nVALUES ( 4, 'Shu','Abbas','Paris','France');<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"700\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssms-create-customers-table-and-select-country-column.jpg\" alt=\"Sample preparation for the SQL Server Insert Into from a Select Query\" class=\"wp-image-11722\" style=\"width:800px;height:auto\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssms-create-customers-table-and-select-country-column.jpg 800w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssms-create-customers-table-and-select-country-column-300x263.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2022\/10\/ssms-create-customers-table-and-select-country-column-768x672.jpg 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><figcaption class=\"wp-element-caption\"><strong><em>Sample preparation for the SQL Server Insert Into from a Select Query<\/em><\/strong><\/figcaption><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-solution-2-sql-server-insert-into-with-multiple-select-and-union-all\">Solution 2 &#8211; Use INSERT INTO SELECTs with UNION ALL in SQL Server<\/h2>\n\n\n\n<p>This solution also makes it easy to read the inserted data because it comes from a simple selection combined with Union All statements. Of course it can also comes from select queries that are not hard coded.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"empty-the-table-using-a-truncate-table-first\"><strong><em>Empty the table using a TRUNCATE TABLE first.<\/em><\/strong><\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO dbo.Customers ( Name, City )\n\tSELECT N'MR MAMMADOU', 'Zurich'   UNION ALL\n\tSELECT N'MR SERGEI', 'Ushuaia'    UNION ALL\n\tSELECT N'MR KARIM', 'Casablanca';<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"solution-2-sql-server-insert-into-from-a-select-from-the-same-table\">Solution 2 &#8211; SQL Server Insert Into from a Select from the same table<\/h2>\n\n\n\n<p>This time, let&#8217;s dd the suffix &#8220;-Junior&#8221; to differentiate the newly inserted rows from the previous ones. Indeed, we must do this to allow insertion without error, because of the Uniqueness constraint added on the Name column.<\/p>\n\n\n\n<p>We simply insert from the same table, so the source and the target are the same unique table : Customers.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO dbo.Customers (Name, City)\n\tSELECT \tNAME + '-Junior', City\n\tFROM \tdbo.Customers;\n<\/pre>\n\n\n\n<p>Finally, you can check all rows, i.e. 3 rows already present from step 2 and 3 new rows of data inserted. The Customers lines inserted into the target table are visible using a simple Select <a href=\"https:\/\/www.dictionary.com\/browse\/query\" target=\"_blank\" rel=\"noreferrer noopener\">query<\/a>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT *\nFROM   dbo.Customers;\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion-on-simple-solutions-to-insert-data-from-selects\">Conclusion on how to use the SQL Server SELECT INTO Query to insert data<\/h2>\n\n\n\n<p>To conclude, different solutions exist in SQL Server to use the Insert Into from a Select statement. The goal is often to enrich tables and to copy data, using hard coded insert statements or select queries from other tables.<\/p>\n\n\n\n<p>Of course those 3 examples use only 3 lines of data and are simple, on purpose. But when dealing with large amounts of data or complex transformations, those queries can be very useful.<\/p>\n\n\n\n<p>So that&#8217;s it for the SQL Server Insert Into from a Select Query article! On a different topic, link to table storage, the T-SQL language also allows you to <a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-get-the-size-of-all-tables-in-sql-server\/\"><strong>display the full list of the tables size and space available in a database<\/strong><\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-server-and-data-tutorials wp-block-embed-sql-server-and-data-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"IEIB2rCTjq\"><a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-get-the-size-of-all-tables-in-sql-server\/\">How to Get the Size of all Tables in SQL Server ?<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;How to Get the Size of all Tables in SQL Server ?&#8221; &#8212; SQL Server and Data Tutorials\" src=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-get-the-size-of-all-tables-in-sql-server\/embed\/#?secret=QVyTXWCVL0#?secret=IEIB2rCTjq\" data-secret=\"IEIB2rCTjq\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>With SQL Server, how to insert data into a table directly from a Select query ? With the MS database SQL Server, Insert Into from a SELECT query is possible using multiple ways. Here we&#8217;ll cover 3 solutions. The <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/sql-server-insert-into-from-a-select\/\" title=\"SQL Server Insert Into from a SELECT with 3 easy queries\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6010,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-9355","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=9355"}],"version-history":[{"count":7,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9355\/revisions"}],"predecessor-version":[{"id":31340,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9355\/revisions\/31340"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6010"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=9355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=9355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=9355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}