CODEWITHSUNDEEP
sql-serverviewunions
Otshepeng Ditshego
Otshepeng Ditshego

Reputation: 197

Create new column in SQL Server view with a default value

I have this query for a view:

SELECT t1.Connected, t1.Region, t1.Homespassed 
FROM connected as t1 
UNION ALL 
SELECT t2.Connected, t2.Region, t2.Homespassed 
FROM connected as t2

I want to add a new column with a default year value. The table should like this:

Connected | Region | Homespassed | Year

Each view that we are unionizing has a different year. Please assist

Upvotes: 1

Views: 7651

Answers (1)

S.Karras
S.Karras

Reputation: 1493

Ok as per your comment, you have a constant value as your year so this will do the trick:

SELECT t1.Connected, t1.Region, t1.Homespassed, '2017' as Year FROM connected as t1 
UNION ALL 
SELECT t2.Connected, t2.Region, t2.Homespassed, '2018' as Year FROM connected as t2;

Or if you want your code to be somewhat better:

DECLARE @YEAR1 CHAR(4), @YEAR2 CHAR(4);
SET @YEAR1 = '2017';
SET @YEAR2 = '2018';

SELECT t1.Connected, t1.Region, t1.Homespassed, @YEAR1 as Year FROM connected as t1 
UNION ALL 
SELECT t2.Connected, t2.Region, t2.Homespassed, @YEAR2 as Year FROM connected as t2;

Upvotes: 3

Related Questions