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).
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.
I hope you
are enjoying with this post! Please share with you friends. Thank you!!
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|