Insert Multiple Records Using One Insert Statement

Type 1:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

Type 2 for SQL server 2008

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)

Clustered and Non-Clustered Index

A table without an index is called a “heap”.

OUTER JOIN

Three different type of OUTER JOIN

1) LEFT OUTER

• Return all rows in Left table with matching data in Right table.
• If the Left table have no matching value in Right table assign null value.

SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.Col1=t2.Col1




2) RIGHT OUTER JOIN

• Return all rows in Right table with matching data in Left table.
• If the Right table have no matching value in Left table assign null value.

SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.Col1=t2.Col1



3) FULL OUTER JOIN

• t returns row from either table when the conditions are met
• and returns null value when there is no match.

INNER -JOIN

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON
t1.Col1=t2.Col1

SELF -JOIN AS OUTER JOIN


SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID

SELF -JOIN AS INNER JOIN

First create database and add data to it.


USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

SQL-Query

SELECT e1.Name EmployerName , e2.Name ManagerName FROM Employee e1 INNER JOIN Employee e2 ON e1.ManagerID=e2.EmployeeID