Hi Folks!!!
Iam going to tell you all the magic of using the union all for the data insertion and how do make a select statement as table and perform joins on it
1.Union ALL
--Create table & insert data
CREATE TABLE SUPERMAN(NAME VARCHAR(50),AGE INT,SSN INT,SHEIGHT INT,SWEIGHT INT,
SNATIVE VARCHAR(50),SHOBBY VARCHAR(50),SJOB VARCHAR(50),SAMBITION VARCHAR(50),SALARY INT)
GO
INSERT INTO SUPERMAN (NAME,AGE,SSN,SHEIGHT,SWEIGHT,SNATIVE,SHOBBY,SJOB,SAMBITION,SALARY)
SELECT 'RAJESH',25,12345,172,75,'TVM','SINGING','SOFTWARE ENGINEER','IAS',500000
UNION ALL
SELECT 'MUTHUSAMY RAJESH',22,12346,160,50,'TVM','SINGING','SOFTWARE ENGINEER','IAS',300000
UNION ALL
SELECT 'OMPRAKASH',25,12347,172,75,'TVM','DRAWING','SCIENTIST','SR.SCIENTIST',5000000
UNION ALL
SELECT 'LAKSHMAN',25,12348,172,75,'TVM','CHATING','SOFTWARE ENGINEER','PROJECT MANAGER',700000
UNION ALL
SELECT 'AKSHAY',1,12349,172,75,'CHENNAI','PLAYING WITH PARENTS','SUPERMAN','SUPERMAN',500000
GO
--Select & test
So you no longer need to use the coventional insert into to every row of insert
2.Use Select query as tables
Steps to demonstrate
1.create another 3 tables & insert the data
CREATE TABLE NAMES(NAME VARCHAR(50))
GO
CREATE TABLE HOBBIES(HNAME VARCHAR(50))
GO
CREATE TABLE JOBS(JNAME VARCHAR(50))
insert into names(name)
SELECT 'RAJESH'
UNION ALL
SELECT 'MUTHUSAMY RAJESH'
UNION ALL
SELECT 'OMPRAKASH'
UNION ALL
SELECT 'LAKSHMAN'
UNION ALL
SELECT 'AKSHAY'
go
insert into hobbies(hname)
SELECT 'SINGING'
UNION ALL
SELECT 'PLAYING WITH PARENTS'
UNION ALL
SELECT 'DRAWING'
UNION ALL
SELECT 'CHATING'
go
insert into jobs(jname)
SELECT 'SOFTWARE ENGINEER'
UNION ALL
SELECT 'SCIENTIST'
UNION ALL
SELECT 'SUPERMAN'
2.WRITE STORED PROCEDURES TO RETRIEVE THE DETAILS
ALTER PROCEDURE SP_TESTER
AS
BEGIN
SELECT A.NAME,A.AGE,A.SSN,A.SHEIGHT,A.SWEIGHT,A.SNATIVE,A.SHOBBY,A.SJOB,A.SAMBITION,A.SALARY,isnull(B.name,'') NCOLS,isnull(C.hname,'') HCOLS,isnull(D.jname,'') JCOLS,E.NAMECOUNT NNAMECOUNT,F.NAMECOUNT HNAMECOUNT,G.NAMECOUNT JNAMECOUNT FROM
((SELECT ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID, * FROM SUPERMAN) A
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID, * FROM names) B ON A.ROWID=B.ROWID
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY hNAME ASC) AS ROWID, * FROM hobbies) C ON A.ROWID=C.ROWID
LEFT OUTER JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY jNAME ASC) AS ROWID, * FROM jobs) D ON A.ROWID=D.ROWID)
LEFT OUTER JOIN
(SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM names) E ON E.ROWID=A.ROWID
LEFT OUTER JOIN
(SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM hobbies) F ON F.ROWID=A.ROWID
LEFT OUTER JOIN
(SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM jobs) G ON G.ROWID=A.ROWID
END
Now closely look into the above stored procedure,you could see the alias on each query and joined with the dynamically generated rowid.Finally it gets called as a single table.
