Sql Query for below requirement

Question

requirement

table1 table is there in databse1 and database2 
in that site column is there
values in site is a,b,c,d,e,f,g,h
if site value is among a,b,c,d then (select * from database1.table1)
else if site value is among e,f,g,h then (select * from database2.table1)

how to write query for this. this query should be written in database3 database
note: only query not sp or function.

---

tsrkreddy

Answer 1

You can write some IF ..ELSE block

IF @par IN ('a','b','c','d','e','f','g,'h')

     SELECT ......

ELSE IF .....

    SELECT......

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Hi sivaramasqlserver,

Regarding your description, the table1 in different databases seem to have the same structure. To somewhat achieve your requirement, you can reference the below query.

DECLARE @siteValue CHAR(1)

;WITH Cte AS
(
SELECT 'abcd' idCol,* FROM DataBase1.schemaName.Table1
UNION ALL
SELECT 'efgh',* FROM DataBase2.schemaName.Table2
)
SELECT * FROM Cte WHERE CHARINDEX(@siteValue,idCol)<>0

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 3

Hi Eric

But using such approach we are preventing for a query optimizer using an index on that column if one exists

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Hi Uri,

Thanks for this reminder.

I don't see any search condition in OP's description, so I just thought a index scan is roughly equivalent to a table scan in this case. Please correct me if I have the wrong idea. :)

Thanks.

---

Eric Zhang
TechNet Community Support

Answer 5

hey here i need to use select query only for our report purpose and should not be taken any variables

---

tsrkreddy

Answer 6

hey here i need to use select query only for our report purpose and should not be taken any variables

---

tsrkreddy

then you need to do like this

select * from database1.table1
where site in ('a,b,c,d')
union all
select * from database2.table1
where site in ('e,f,g,h')

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page