Click here to Skip to main content
Click here to Skip to main content

Use Union All & Select Queries Effectively

By , 2 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

rajeshitpro
Software Developer (Senior) Cognizant Technology Solutions
India India
Expertise in Asp.Net,Worked in variety of domains.
Have fair knowledge in using design patterns.
Presently he is working as Senior developer with cognizant India.His interest is to develop tools using Sqlserver,C#.Net.

Comments and Discussions

 
GeneralReason for my vote of 3 Saw this also in an email alert from... PinmemberGraGra_332-Aug-10 18:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 2 Aug 2010
Article Copyright 2010 by rajeshitpro
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid