{"id":165,"date":"2019-07-09T16:56:24","date_gmt":"2019-07-09T11:26:24","guid":{"rendered":"https:\/\/techieshouts.com\/?p=165"},"modified":"2022-08-09T19:08:33","modified_gmt":"2022-08-09T13:38:33","slug":"creating-table-using-select-into","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/creating-table-using-select-into\/","title":{"rendered":"CREATE TABLE using SELECT INTO in MS SQL Server"},"content":{"rendered":"\n<p>Microsoft SQL Server provides a nice feature of creating a table on the fly along with the data from another table using &#8220;Select into&#8221; statement.<\/p>\n\n\n\n<h2><strong>Syntax<\/strong><\/h2>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT columns \nINTO newTableName \nFROM existingTableName \nWHERE conditions;<\/pre>\n\n\n\n<p>columns &#8211; Columns to be retrieved from the existing table.&nbsp;<\/p>\n\n\n\n<p>newTableName &#8211; Name of the new table to be created.&nbsp; <\/p>\n\n\n\n<p>existingTableName &#8211; Source table with data.&nbsp; <\/p>\n\n\n\n<p>conditions &#8211; Conditions to filter the data from the existing table. But, this is not a mandatory syntax. <\/p>\n\n\n\n<p>It is important to note that when executing the SELECT INTO statement, the new table will be created <strong>without the keys and constraints<\/strong>. It will copy only the columns from the existing table.&nbsp;<\/p>\n\n\n\n<h2><strong>Creating with simple SELECT<\/strong><\/h2>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT countryName, countryCode \nINTO tblTeam  \nFROM tblCountry;<\/pre>\n\n\n\n<p>Executing the above query creates the table &#8216;tblTeam&#8217; with the columns (countryName and countryCode) from the table &#8216;tblCountry&#8217; and also populates all the records. <\/p>\n\n\n\n<p>To rename the column names in the Team table, use a column alias in the select clause.&nbsp;<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT countryName AS Team --Rename the column 'countryName' to 'Team' \nINTO tblTeam  \nFROM tblCountry;<\/pre>\n\n\n\n<p>New column can also be created\nlike NULL AS teamCode or &#8221; AS\nteamCode<\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT countryName AS Team, \r\nNULL AS teamCode --'teamCode' Column is created with NULL values\r\nINTO tblTeam \r\nFROM tblCountry<\/pre>\n\n\n\n<h2><strong>Creating with a filter condition<\/strong><\/h2>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT countryName AS Team, countryCode AS teamCode \nINTO tblTeam  \nFROM tblCountry \nWHERE ID &lt; 10  --Copies the records with ID &lt; 10;<\/pre>\n\n\n\n<p>Using the WHERE condition, we can filter and populate the data from the country table into the team table. The above query populates records with the ID less than 10. <\/p>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">SELECT countryName AS Team, countryCode AS teamCode \nINTO tblTeam  \nFROM tblCountry \nWHERE 1 = 2  --False condition. Copies only the structure;<\/pre>\n\n\n\n<p>As the where condition returns false in the above query, data will not be populated into the team table. It copies only the table structure.&nbsp;<\/p>\n\n\n\n\n\n<p>Also, check &#8220;<a href=\"https:\/\/techieshouts.com\/create-table-in-sql-server\/\">CREATE TABLE types in MS SQL Server<\/a>&#8220;<\/p>\n\n\n\n<p>Reference &#8211;  <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/queries\/select-into-clause-transact-sql?view=sql-server-2017\" target=\"_blank\" rel=\"noopener noreferrer\">Microsoft SQL Server Select into<\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft SQL Server provides a nice feature of creating a table on the fly along with the data from another table using &#8220;Select into&#8221; statement. Syntax columns &#8211; Columns to be retrieved from the existing table.&nbsp; newTableName &#8211; Name of the new table to be created.&nbsp; existingTableName &#8211; Source table with data.&nbsp; conditions &#8211; Conditions\u2026 <span class=\"read-more\"><a href=\"https:\/\/techieshouts.com\/home\/creating-table-using-select-into\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[42,39,40],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/165"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":21,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":599,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/165\/revisions\/599"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}