Query logic

hi team,
need your suggestion in following scenario.

i have two tables as below with data

select id , name , loacation, preference from tbl_maindata

id name location preference
a0001 GSc1 del a1
a0002 gsc2 del a1
a0003 gsc3 del a1
b0001 GSc1 mum bl
ab002 gsc2 mum bl
b0003 gsc3 mum bl

select * from id, name , loacation, preference from tbl_seprator where name = 'sep'

id name location preference
s00009 sep sep sep

i need data from tbl_maindata and when a particular lacation and preference is ended then a record from tbl_seprator on the base of above query
should be appear there.

id name location preference
a0001 GSc1 del a1
a0002 gsc2 del a1
a0003 gsc3 del a1
s00009 sep sep sep
b0001 GSc1 mum bl
ab002 gsc2 mum bl
b0003 gsc3 mum bl
s00009 sep sep sep

UNION will do that, no?

SELECT id , name , location, preference FROM tbl_maindata
UNION
SELECT id , name , location, preference FROM tbl_seprator where name = 'sep'

As long as the columns are the same, this will return the results from the first and bottom query as one result set. See: http://www.w3schools.com/sql/sql_union.asp

declare @tbl_MainData TABLE
(id VARCHAR(50) PRIMARY KEY CLUSTERED,
 name VARCHAR(50),
 location VARCHAR(3),
 preference VARCHAR(3))

declare @tbl_Seprator AS TABLE
(id VARCHAR(50) PRIMARY KEY CLUSTERED,
 name VARCHAR(50),
 location VARCHAR(3),
 preference VARCHAR(3))

INSERT INTO @tbl_MainData(id,name,location,preference)
VALUES('a0001', 'GSc1', 'del', 'a1'),
      ('a0002', 'gsc2', 'del', 'a1'),
      ('a0003', 'gsc3', 'del', 'a1'),
      ('b0001', 'GSc1', 'mum', 'bl'),
      ('ab002', 'gsc2', 'mum', 'bl'),
      ('b0003', 'gsc3', 'mum', 'bl')

INSERT INTO @tbl_Seprator(id,name,location,preference)
VALUES('s00009', 'sep', 'sep', 'sep')


SELECT
    id,name,location,preference 
    --,DR,orderLevel
FROM
(
SELECT id,name,location,preference 
    ,DENSE_RANK()OVER(ORDER BY location,preference) AS DR
    ,0 AS orderLevel    
FROM
    @tbl_MainData

UNION ALL

SELECT S.id,S.name,S.location,S.preference 
    ,DENSE_RANK()OVER(ORDER BY MD.location,MD.preference) AS DR
    ,1
FROM
    (SELECT location,preference FROM @tbl_MainData GROUP BY location,preference ) AS MD
CROSS JOIN
    (SELECT id,name,location,preference FROM @tbl_Seprator WHERE name = 'sep') S
)F
ORDER BY F.DR, F.orderLevel

the output :

id    name    location    preference
a0001    GSc1    del    a1
a0002    gsc2    del    a1
a0003    gsc3    del    a1
s00009    sep    sep    sep
ab002    gsc2    mum    bl
b0001    GSc1    mum    bl
b0003    gsc3    mum    bl
s00009    sep    sep    sep

Here's an alternative:

SELECT 
    CASE WHEN md_last.location IS NULL THEN md.id ELSE sep.id END AS id,
    CASE WHEN md_last.location IS NULL THEN md.name ELSE sep.name END AS name,
    CASE WHEN md_last.location IS NULL THEN md.location ELSE sep.location END AS location,
    CASE WHEN md_last.location IS NULL THEN md.preference ELSE sep.preference END AS preference
FROM tbl_maindata md
LEFT OUTER JOIN (    
    SELECT location, preference, MAX(id) AS id
    FROM tbl_maindata
    GROUP BY location, preference
) AS md_last ON
    md_last.location = md.location AND
    md_last.preference = md.preference AND
    md_last.id = md.id
OUTER APPLY (
    SELECT md.id, md.name, md.location, md.preference, 1 AS sort_order
    WHERE
        md_last.location IS NOT NULL
    UNION ALL
    SELECT id, name, location, preference, 2 AS sort_order
    FROM tbl_separator
    WHERE
        md_last.location IS NOT NULL
) AS sep
ORDER BY md.location, md.preference, sep.sort_order, md.id