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!!
