September 24, 2012

SELECT INTO

My previous blog post was about using insert in many ways. You can use SELECT INTO to add data from existing table to a new table. In this statement SQL Server will create a table for you. You just want to write the query and add INTO clause to the statement. Rest will done by SQL Server for you.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee to non existing table
SELECT [BusinessEntityID],
[LoginID],
[OrganizationNode],
[JobTitle],
[BirthDate],
[MaritalStatus],
[Gender]
INTO Tmp
FROM HumanResources.Employee
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

If you are trying to add data into a existing table by using this INTO clause, you will have a error. You can join two table and add data into new table too.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT HE.[BusinessEntityID],
HE.[LoginID],
HE.[Gender],
PP.[FirstName],
PP.[NameStyle]
INTO Tmp
FROM [HumanResources].[Employee] AS HE
INNER JOIN Person.Person AS PP
ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

And you can use conditions as well.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT HE.[BusinessEntityID],
HE.[LoginID],
HE.[Gender],
PP.[FirstName],
PP.[NameStyle]
INTO Tmp
FROM [HumanResources].[Employee] AS HE
INNER JOIN Person.Person AS PP
ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
WHERE HE.[BusinessEntityID] BETWEEN 70 AND 80
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

When you are joining two tables consider about the column names too. If you are using same name to two or more columns SQL Server will occur error. Below code will generate such a error.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT HE.[BusinessEntityID],
HE.[LoginID],
HE.[Gender],
PP.[BusinessEntityID],
PP.[FirstName],
PP.[NameStyle]
INTO Tmp
FROM [HumanResources].[Employee] AS HE
INNER JOIN Person.Person AS PP
ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

No comments:

Post a Comment