Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Tip/Trick

Use Union All & Select Queries Effectively

Rate me:
Please Sign up or sign in to vote.
4.00/5 (3 votes)
2 Aug 2010CPOL 11.5K   4   1
Union All,Select
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)


Written By
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... Pin
Graeme_Grant2-Aug-10 18:10
mvaGraeme_Grant2-Aug-10 18:10 

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

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