How to use multiple tables using specific condition

0
votes

I have two table TableA and TableB for these tables data will be populated based on certain process (means TableA will be like temp table if value finialised then data will be moved to TableB)

based on that X condition I will select the value from TableA else from TableB

If X=10 condition 
select col1,col2,col3,col4 from TableA
else
select col1,col2,col3,col4 from TableB

Both tables TableA and TableB will have same table structure,So here i dont want use the same code statement again in else by changing the table name alone.Is any other way that can be used without repeating the select statement again by changing the table.

4
sqlsql-servertsql
Please provide sample data and desired output. Without this, it is very hard to understand, what you need. Also you should show what you have tried. - Jonathan Willcock
@JonathanWillcock its nothing related to data its all about the syntax which i am using that i have mentioned code block.Is there any other alternate way which can be used instead of the code i have mentioned. - Sowbarani Karthikeyan

4 Answers

1
votes

I think you want this:

DECLARE @tableName varchar(20)
IF 1=1
set @tableName = 'table1'
ELSE
set @tableName = 'table2'

EXEC ('SELECT * FROM ' + @tableName)
1
votes

or you can also try:

DECLARE @x AS INT
DECLARE @sqlquery AS NVARCHAR(100)

SET @x = 10 --ignore this if you already have a value for x

SET @sqlquery = 'SELECT * FROM '+ CASE WHEN @x = 10 THEN 'TableA' ELSE 'tableB' END

EXEC SP_EXECUTESQL @sqlquery

you can read also: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

0
votes

You can simply roll your condition into a UNION statement.

If @condition = 1 we only select from tableA, else we only select from tableB.

DECLARE @condition bit

SET @condition = 0

SELECT * FROM  [dbo].[TableA] WHERE @condition = 1
UNION
SELECT * FROM  [dbo].[TableB] WHERE @condition != 1
0
votes

The way I would achieve this is with a variable and a union query. The variable allows you to give a true false condition for your where clause so everything is returned from the table you want and nothing from the table you don't want. The structure of the tables allows you to use the query in a union statement so you can see all of the code in one place.

Declare @x int = 10
Select col1, col2, col3, col4 from TableA where @x=10
Union
Select col1, col2, col3, col4 from TableB where @x<>10;