Suggested Videos
Part 94 - SQL Server trigger execution order
Part 95 - Audit table changes in sql server
Part 96 - Logon triggers in sql server
In this video we will discuss the power and use of SELECT INTO statement in SQL Server.
We will be using the following 2 tables for the examples.
SQL Script to create Departments and Employees tables
The SELECT INTO statement in SQL Server, selects data from one table and inserts it into a new table.
SELECT INTO statement in SQL Server can do the following
1. Copy all rows and columns from an existing table into a new table. This is extremely useful when you want to make a backup copy of the existing table.
2. Copy all rows and columns from an existing table into a new table in an external database.
3. Copy only selected columns into a new table
4. Copy only selected rows into a new table
5. Copy columns from 2 or more table into a new table
6. Create a new table whose columns and datatypes match with an existing table.
7. Copy all rows and columns from an existing table into a new table on a different SQL Server instance. For this, create a linked server and use the 4 part naming convention
Please note : You cannot use SELECT INTO statement to select data into an existing table. For this you will have to use INSERT INTO statement.
Part 94 - SQL Server trigger execution order
Part 95 - Audit table changes in sql server
Part 96 - Logon triggers in sql server
In this video we will discuss the power and use of SELECT INTO statement in SQL Server.
We will be using the following 2 tables for the examples.
SQL Script to create Departments and Employees tables
Create table Departments
(
DepartmentId int primary key,
DepartmentName nvarchar(50)
)
Go
Insert into Departments values (1, 'IT')
Insert into Departments values (2, 'HR')
Insert into Departments values (3, 'Payroll')
Go
Create table Employees
(
Id int primary
key,
Name nvarchar(100),
Gender nvarchar(10),
Salary int,
DeptId int foreign key references Departments(DepartmentId)
)
Go
Insert into Employees values (1, 'Mark', 'Male', 50000, 1)
Insert into Employees values (2, 'Sara', 'Female', 65000, 2)
Insert into Employees values (3, 'Mike', 'Male', 48000, 3)
Insert into Employees values (4, 'Pam', 'Female', 70000, 1)
Insert into Employees values (5, 'John', 'Male', 55000, 2)
Go
The SELECT INTO statement in SQL Server, selects data from one table and inserts it into a new table.
SELECT INTO statement in SQL Server can do the following
1. Copy all rows and columns from an existing table into a new table. This is extremely useful when you want to make a backup copy of the existing table.
SELECT * INTO
EmployeesBackup FROM Employees
2. Copy all rows and columns from an existing table into a new table in an external database.
SELECT * INTO HRDB.dbo.EmployeesBackup FROM Employees
3. Copy only selected columns into a new table
SELECT Id, Name, Gender INTO EmployeesBackup FROM
Employees
4. Copy only selected rows into a new table
SELECT * INTO
EmployeesBackup FROM Employees WHERE DeptId = 1
5. Copy columns from 2 or more table into a new table
SELECT * INTO
EmployeesBackup
FROM Employees
INNER JOIN Departments
ON Employees.DeptId = Departments.DepartmentId
6. Create a new table whose columns and datatypes match with an existing table.
SELECT * INTO
EmployeesBackup FROM Employees WHERE 1 <> 1
7. Copy all rows and columns from an existing table into a new table on a different SQL Server instance. For this, create a linked server and use the 4 part naming convention
SELECT * INTO TargetTable
FROM
[SourceServer].[SourceDB].[dbo].[SourceTable]
Please note : You cannot use SELECT INTO statement to select data into an existing table. For this you will have to use INSERT INTO statement.
INSERT INTO ExistingTable (ColumnList)
SELECT ColumnList
FROM SourceTable
Sir,
ReplyDeletePlease discuss CLR Trigger.
Thanks
Hi venkat,
ReplyDeletePlease include some videos on understanding execution plan and performance tuning concepts.
Thanks
Baskar M
Hello Venkat,
ReplyDeleteThank you for all your videos, I'm sure everyone makes use of them in his own way, so keep up the great work man :)
I have a question regarding "1 <> 1" to create the table with same schema as the original table: I use "TOP 0" to get the schema of tables (e.g.: "SELECT TOP 0 * FROM MyTable"), and I tried using TOP 0 instead of "WHERE 1 <> 1" and it worked, and so my question is: what is the best way to get the schema to the original table? is it via using "TOP 0" or via using "WHERE 1 <> 1" or something else?
Thank you again.
Hi,
ReplyDeleteplease discus the CLE Trigger,,,
Hello Sir,
ReplyDeleteplease include some video on understanding execution plan & performance tuning concept.
regard,
Gaurav