Merge tables in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Merge tables in SP

Author

Topic

just.net
Starting Member

24 Posts

Posted - 2009-04-29 : 08:16:19

Hello,
I want to do a search in my website, i need to perform
Search in different tables, the columns names in the results will
be the same because i am doing merge between the tables(in c# code),
How can i do merge (or something like that) in the SP ?
(instead of doing 5 or 6 SELECT to the DB in c# code and then merge)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-29 : 08:41:48

Use UNION, e.g:


select a,b,c from a
UNION ALL
select a,b,c from b

just.net
Starting Member

24 Posts

Posted - 2009-04-29 : 10:03:51

thanks, this UNION ALL is great, it's working.
but i forgot one thing, i also need to know in the
results which table each results are belong to, for example:
ID Name Type
42424 Dan Clients
s32422 Danda Clients
24234 Dana Clients
42424 Dans users
34234 Dant users
97797 Dann users
45255 Danb suppliers
425808 Danccc suppliers
423423 Danqqww suppliers

I need this because i need to redirect the user to
the correct page according to the type, so i creared something
that i just learned:
ALTER PROCEDURE [dbo].[spPlutoSearch]
@parameter nvarchar(50) = NULL
AS
DECLARE @type nvarchar(10)
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Results
(
ID int,
Name nvarchar(50),
Email nvarchar(50)
)
SET @type = 'Clients'

INSERT INTO #Results(ID, Name, Email)
SELECT UserID AS ID, UserName AS Name, Email
FROM UsersAuthorizationsView

INSERT INTO #Results(ID, Name, Email)
SELECT ClientID AS ID, LastFirstPet AS Name, Email
FROM SearchResultView

INSERT INTO #Results(ID, Name)
SELECT PetID AS ID, Name AS Name
FROM SearchResultView

SELECT * FROM Results
WHERE ID LIKE '%' + @parameter + '%' OR Name LIKE '%' + @parameter + '%'
END

Now after this, how can i insert the @type 'Clients' (e.g)
INTO the temp table?
(i can add the column 'Type' to all of
the tables, but it seems not right)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-29 : 10:33:17

That is the only way to do it. You can also do this with the Union instead:



select a.ID, a.Name, a.Email, a.TabName
FROM (
SELECT UserID AS ID, UserName AS Name, Email, 'users' as TabName
FROM UsersAuthorizationsView
UNION ALL
SELECT ClientID AS ID, LastFirstPet AS Name, Email, 'clients' as TabName
FROM SearchResultView
UNION ALL
SELECT PetID AS ID, Name AS Name, NULL as Email, 'suppliers' as TabName
FROM SearchResultView) a
WHERE a.ID LIKE '%' + @parameter + '%' OR a.Name LIKE '%' + @parameter + '%'

just.net
Starting Member

24 Posts

Posted - 2009-04-29 : 11:36:10

i did it. thanks to you!

ALTER PROCEDURE [dbo].[spPlutoSearch]
@parameter nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #ResultsUsers
(
ID int,
FullName nvarchar(50),
ObjectType nvarchar(10) Default 'Users'
)
CREATE TABLE #ResultsClients
(
ID int,
FullName nvarchar(50),
ObjectType nvarchar(10) Default 'Clients'
)
CREATE TABLE #ResultsPets
(
ID int,
FullName nvarchar(50),
ObjectType nvarchar(10) Default 'Pets'
)
INSERT INTO #ResultsUsers(ID, FullName)
SELECT UserID AS ID, FullName
FROM UsersAuthorizationsView

INSERT INTO #ResultsClients(ID, FullName)
SELECT ClientID AS ID, FullName
FROM ClientsView

INSERT INTO #ResultsPets(ID, FullName)
SELECT PetID AS ID, Name
FROM PetsView

SELECT *
FROM(SELECT * FROM #ResultsUsers
UNION ALL
SELECT * FROM #ResultsClients
UNION ALL
SELECT * FROM #ResultsPets) AS A
WHERE ID LIKE '%' + @parameter + '%' OR FullName
LIKE '%' + @parameter + '%'
END

Thanks!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-29 : 11:59:17

No problem, but you do not need the Inserts into the #temp tables, you are just using extra resource doing it that way.

Instead of all the code you have, you could simply do:


ALTER PROCEDURE [dbo].[spPlutoSearch]
@parameter nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT 
	a.ID, 
	a.FullName, 
	a.ObjectType
FROM (
SELECT UserID AS ID, FullName, 'Users' as ObjectType FROM UsersAuthorizationsView
UNION ALL
SELECT ClientID AS ID, FullName, 'Clients' as ObjectType FROM ClientsView
UNION ALL
SELECT PetID AS ID, Name AS FullName, 'Pets' as ObjectType FROM PetsView) a
WHERE a.ID LIKE '%' + @parameter + '%' OR a.FullName LIKE '%' + @parameter + '%'

just.net
Starting Member

24 Posts

Posted - 2009-04-29 : 12:31:15

I thought your query will not work because of this:
'Users' as ObjectType, but i tried and its worked!!
i also missed this part: SELECT a.ID
now its much better:
SELECT results.ID, results.FullName, results.ObjectType
FROM (
SELECT UserID AS ID, FullName, 'Users' as ObjectType FROM UsersAuthorizationsView
UNION ALL
SELECT ClientID AS ID, FullName, 'Clients' as ObjectType FROM ClientsView
UNION ALL
SELECT PetID AS ID, Name AS FullName, 'Pets' as ObjectType FROM PetsView) AS results
WHERE results.ID LIKE '%' + @parameter + '%' OR results.FullName LIKE '%' + @parameter + '%'.

thank you very very much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources