Select Into Statement in SQL Server(TSQL)

dbtut

6 years ago

We use the Select Into Statement to export the data in a table to another table. You can see a few examples below.

First, we are creating a table and inserting some data into it for using in our test:

Use [TestDB]
GO
CREATE TABLE [dbo].[MyTable]
(Name VARCHAR(25))
GO
INSERT INTO [dbo].[MyTable] VALUES ('Nurullah CAKIR'),('Faruk ERDEM')

Use [TestDB]

CREATE TABLE [dbo].[MyTable]

(Name VARCHAR(25))

INSERT INTO [dbo].[MyTable] VALUES ('Nurullah CAKIR'),('Faruk ERDEM')

Example1: We are creating a table named MyTableNew with the following script. We transfer the values in the Name column of the MyTable table to the MyTableNew table. If we put * instead of Name, it will transfer the values of all columns in the table to the new table.

SELECT [Name] INTO MyTableNew FROM MyTable
GO
SELECT * FROM MyTableNew

SELECT [Name] INTO MyTableNew FROM MyTable

SELECT * FROM MyTableNew

Example 2: With the following script, we create a table named MyTableNew in the TestDB2 database and transfer the values in the Name column of the MyTable of the TestDB database to the MyTableNew of the TestDB2 database.

SELECT [Name] INTO TestDB2.dbo.MyTableNew FROM TestDB.dbo.MyTable
GO
SELECT * FROM TestDB2.dbo.MyTableNew

SELECT [Name] INTO TestDB2.dbo.MyTableNew FROM TestDB.dbo.MyTable

SELECT * FROM TestDB2.dbo.MyTableNew

Example3: We are creating a table named MyTableNew with the following script. We pass the values in the Name column of the MyTable to the MyTableNew . Unlike the above examples, we created the Name column as Name_Surname in the new table.

SELECT [Name] AS [Name_Surname] INTO MyTableNew FROM MyTable
GO
SELECT * FROM MyTableNew

SELECT [Name] AS [Name_Surname] INTO MyTableNew FROM MyTable

SELECT * FROM MyTableNew

Example4: If we just want to create the table and don’t want to transfer the data, we can do this with the help of the following script. In the example below, we can only export a specific set of data in the table by writing a condition in the Where condition.

SELECT [Name] AS [Name_Surname] INTO MyTableNew FROM MyTable Where 1=0
GO
SELECT * FROM MyTableNew

SELECT [Name] AS [Name_Surname] INTO MyTableNew FROM MyTable Where 1=0

SELECT * FROM MyTableNew