SQL Server SELECT INTO Statement [How To]

What is SQL “SELECT INTO”? And when you Use “SELECT INTO”?

In this Article, I going to share the detail about SQL Server “SELECT INTO” Statement and detail as following as.

SQL SELECT INTO is use to “SELECT” data from one table and “INSERT” into a new table and also creates a new table located in the default filegroup. It is copied all columns records with same data types also.

It is only “INSERT” the values of existing tables “INTO” a new table.

SQL SELECT INTO statement can also use to “Copy” data from more than one table into a new table that means you can use “SQL JOINS” to “SELECT” records from multiple tables and “INSERT” in a single table.

SQL SELECT INTO statement can also use to CREATE a “new”, “empty” table using another table schema. You can just add a “WHERE” clause that is return no records (empty rows).

Stayed Informed - Create Country Table & Insert 192 Countries Data!

Note:-

The “New Table” must not exist in the querying database. If it already exists, the SELECT INTO statement will throw an error.

According to Microsoft docs, “The FILESTREAM attribute does not transfer when you create a new table by using the SELECT INTO statement. FILESTREAM BLOBs are copied and stored in the new table as varbinary (max) BLOBs. If a FILESTREAM BLOB exceeds 2 GB, the following error message is raised and the statement is stopped: "Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.””

Examples As,

--Syntax:-
SELECT ColumnNames  INTO NewTableName
  FROM OldTableName
WHERE [Your_Candtions]

--Query 1:-
SELECT TOP 20 * FROM [Test].[dbo].[Countries]

Result:-
Id    CountryID      CountryName       Nationality
-------------------------------------------
1 AD         Andorra Andorran
2 AE         United Arab Emirates Emirian
3 AF         Afghanistan Afghan
4 AG         Antigua and Barbuda Antiguans, Barbudans
5 AL         Albania Albanian
6 AM         Armenia Armenian
7 AO         Angola Angolan
8 AR         Argentina Argentinean
9 AT         Austria Austrian
10 AU         Australia Australian
11 AZ         Azerbaijan Azerbaijani
12 BA         Bosnia and Herzegovina Bosnian, Herzegovinian
13 BB         Barbados Barbadian
14 BD         Bangladesh Bangladeshi
15 BE         Belgium Belgian
16 BF         Burkina Faso Burkinabe
17 BG         Bulgaria Bulgarian
18 BH         Bahrain Bahraini
19 BI         Burundi Burundian
20 BJ         Benin Beninese

--Query 2:-
SELECT TOP 10 * INTO CountriesCopy
  FROM [Test].[dbo].[Countries]

--Query 3:-
--COPY TOP 10 RECORDS FROM COUNTRIES TABLE AND INSERTED IN THE "COUNTRIESCOPY" NEW TABLE.
SELECT * FROM [dbo].[CountriesCopy]

--Query 3 Result:-
Id CountryID CountryName Nationality
------------------------------------------------------
1 AD         Andorra      Andorran
2 AE         United Arab Emirates Emirian
3 AF         Afghanistan Afghan
4 AG         Antigua and Barbuda Antiguans, Barbudans
5 AL         Albania Albanian
6 AM         Armenia Armenian
7 AO         Angola Angolan
8 AR         Argentina Argentinean
9 AT         Austria Austrian
10 AU         Australia Australian

The Query 1 result and Query 3 result are same that means copies all the data from an existing “COUNTRIES” table INTO “COUNTRIESCOPY” new table.

Stayed Informed – Best SQL Server Tutorials, Examples and QA also!

I hope you are enjoying with this post! Please share with you friends. Thank you!!