DML INSERT with multiple ways in SQL Server 2005: (Multiple INSERT statement)

Whoever is aware with SQL-Server must be aware with simple INSERT statement. Generally we used INSERT records in table with either of the following SQL statements.
–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

1.)

–must give column value in sequence of column defined in table.

Insert into Car values (‘Honda CRV’,‘SUV’)

OR

2.)

–you can change the sequence order of column after table name

–and can give value in defined order in INSERT statement

Insert into Car (CarName,CarDesc) values (‘Honda Pilot’,‘SUV’)

3.)

Above are the common INSERT statement and widely used as well. Apart from these, there are several ways for INSERT statement.

With SELECT……..UNION ALL

INSERT INTO Car(CarName,CarDesc)

SELECT ‘Toyota a’,‘toyota’ UNION ALL

SELECT ‘Toyota b’,‘toyota’ UNION ALL

SELECT ‘Toyota C’,‘toyota’

4.)

Suppose you have one more table from which you want to populate your CAR table.

–create first table for demonstration

CREATE TABLE CAR_Master

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

INSERT INTO Car_Master(CarName,CarDesc)

SELECT ‘Mercedes s’,‘Mercedes’ UNION ALL

SELECT ‘Mercedes c’,‘Mercedes’

Now, I will populate CAR table from CAR_Master table.

INSERT INTO Car(CarName,CarDesc)

SELECT CarName,CarDesc FROM Car_Master where CarName like ‘m%’

5.)

If you wish to populate your INSERT statement with stored procedure than do follow the below given query.

–Create SP which will return result set

–NOTE: You can use multiple resultset in one SP

–and all the records will be INSERTed to our table

–Make sure, you have same number of column with same datatype in

–all record set you choose in SP

CREATE PROC CarNames

AS

SET NOCOUNT ON

SELECT CarName,CarDesc FROM Car_Master

RETURN

Now simply, I can run following INSERT query.

INSERT INTO CAR (CarName,CarDesc)

EXEC carnames

Reference: Ritesh Shah

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT ‘MIS1’,‘MIS1’ UNION ALL

SELECT ‘MIS2’,‘MIS2’

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT ‘Ritesh’,1,50000 UNION ALL

SELECT ‘Bihag’,2,29000 UNION ALL

SELECT ‘Rajan’,1,30000 UNION ALL

SELECT ‘Alka’,2,19000

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

INSERT INTO Project(projName,deptNo)

SELECT ‘A’,1 UNION ALL

SELECT ‘B’,2 UNION ALL

SELECT ‘C’,1

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as ‘AverageSalary/DEPT’

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

Reference: Ritesh Shah