SQL SELECT INTO Statement
SQL SELECT INTO Statement will create a backup of existed table.
Syntax:
SELECT <COLUMN NAMES>
INTO <NEW TABLE NAME>
FROM <OLD TABLE NAME>
WHERE <CONDITIONS>
Example 1:
USE AdventureWorks
GO
SELECT *
INTO dbo.Address_Backup
FROM Person.Address
GO
The above statement will create a backup table for Person.Address table with the name of Address_Backupin dbo schema.
Example 2:
SELECT *
INTO Tbl_Backup
FROM (
SELECT 1 ID,'Gustavo' NAME UNION ALL
SELECT 2 ,'Kim'UNION ALL
SELECT 3 ,'Michael'UNION ALL
SELECT 4 ,'Benjamin'
) AS Tbl
In the above example, we are going to create a table with the name of Tbl_Backup .But the resulted values are not from a UDT. After executing the query, if we check out the Tbl_Backup Design/Properties (Alt+F1), we can observe ID data type is INT and NAME data type is VARCHAR (8).
Why the Name Data type is Varchar (8)?
In the result set Max length of NAME column is 8. (‘Benjamin’)
Means SQL Server consider the Max length of the column as Data type length.
Example 3:
IF OBJECT_ID('EmpDetails') IS NOT NULL DROP TABLE EmpDetails
GO
IF OBJECT_ID('EmpDetails_Bak') IS NOT NULL DROP TABLE EmpDetails_Bak
GO
Create Table EmpDetails(ID Int IDENTITY, Name Varchar(100), HRA MONEY,[BASIC] MONEY, SAL AS HRA+BASIC, HDate DATETIME NOT NULL DEFAULT GETDATE())
GO
INSERT INTO EmpDetails(Name, HRA ,BASIC)
VALUES ('Gustavo', 4500 , 9000 )
,('Kim', 5000 , 10000 )
,('Michael', 4000 , 8000 )
GO
SELECT *
INTO EmpDetails_Bak
FROM EmpDetails
GO
SELECT * FROM EmpDetails_Bak
GO
In the above example,
| Column Name |
EmpDetails |
EmpDetails_Bak |
| ID |
Identity |
Identity |
| Name |
Varchar(100) |
Varchar(100) |
| HRA |
Money |
Money |
| BASIC |
Money |
Money |
| SAL |
Money(Computed Column) |
Money |
| HDate |
DateTime(Default getdate()) |
Datetime |
The table EmpDetails_Bak structure is differs from EmpDetails as shown in the above list.
It means, when we using SELECT INTO statement, the backup table won’t get the same structure as Main table.
Example 4:
IF OBJECT_ID('EmpDetails')IS NOT NULL DROP TABLE EmpDetails
GO
IF OBJECT_ID('tempdb..#EmpDetails_OPClause') IS NOT NULL
DROP TABLE #EmpDetails_OPClause
GO
IF OBJECT_ID('tempdb..#EmpDetails_OPClauseUpdate') IS NOT NULL
DROP TABLE #EmpDetails_OPClauseUpdate
GO
Create Table EmpDetails(ID Int IDENTITY, Name Varchar(100), HRA MONEY,BASIC MONEY, SAL AS HRA+BASIC, HDate DATETIME NOT NULL DEFAULT GETDATE())
GO
Create Table #EmpDetails_OPClause(ID Int, Name Varchar(100), HRA MONEY,BASIC MONEY, SAL MONEY, HDate DATETIME)
GO
Create Table #EmpDetails_OPClauseUpdate(ID Int, OldName Varchar(100),NewName Varchar(100))
GO
--0.01
INSERT INTO EmpDetails(Name, HRA ,BASIC)
OUTPUT inserted.*
INTO #EmpDetails_OPClause
VALUES ('Gustavo', 4500 , 9000 )
,('Kim', 5000 , 10000 )
,('Michael', 4000 , 8000 )
GO
select * from #EmpDetails_OPClause
GO
Update EmpDetails
Set Name ='Michael J'
OUTPUT inserted.ID ID , deleted.Name 'OldName', inserted.Name 'NewName'
INTO #EmpDetails_OPClauseUpdate
WHERE ID = 3
GO
SELECT*FROM #EmpDetails_OPClauseUpdate
GO
In this example, we are using INTO statement for OUTPUT Clause.
We are inserting the inserted records into #EmpDetails_OPClause table. (0.01)
Here we are inserting old value and New value into #EmpDetails_OPClauseUpdate table.
–Cleaning Tables
DROP TABLE Address_Backup , Tbl_Backup,EmpDetails ,EmpDetails_Bak
Notes:
- New table name should not exist in the database. . Otherwise it will thorugh the following error message.
Msg 2714, Level 16, State 6, Line 1 There is already an object named ‘EmpDetails_Bak’ in the database.
- Select statement should have the column names. Otherwise it will through the following error message.
Msg 1038, Level 15,State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
Like this:
Like Loading...
