I want to create view with union of three tables. But in result query I want one extra column like 'tableId'.
My code is like
CREATE OR REPLACE VIEW DETAILS
AS SELECT
* FROM
(
SELECT
T1.ID,
T1.AMOUNT,
T1.STATUS,
T1.ADDEDBY,
T1.ADDEDON
FROM Table1 T1
UNION ALL
SELECT
T2.ID,
T2.AMOUNT,
T2.STATUS,
T2.ADDEDBY,
T2.ADDEDON
FROM Table2 T2
UNION ALL
SELECT
T3.ID,
T3.BILLAMOUNT,
T3.STATUS,
T3.ADDEDBY,
T3.ADDEDON
FROM Table3 T3
);
This gives me union of required three tables. But how can i get table Id column in resulted output? This column is not present in any of the three tables.
In ALTER VIEW statement, we can add new columns, or we can remove the columns without deleting the view. By using ALTER VIEW statement, we can change the Structure of the view.
In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table.
How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.
It sounds like you just want to add an additional hard-coded value to your SELECT
list
CREATE OR REPLACE VIEW DETAILS
AS SELECT
* FROM
(
SELECT
T1.ID,
T1.AMOUNT,
T1.STATUS,
T1.ADDEDBY,
T1.ADDEDON,
'T1' tableID
FROM Table1 T1
UNION ALL
SELECT
T2.ID,
T2.AMOUNT,
T2.STATUS,
T2.ADDEDBY,
T2.ADDEDON,
'T2' tableID
FROM Table2 T2
UNION ALL
SELECT
T3.ID,
T3.BILLAMOUNT,
T3.STATUS,
T3.ADDEDBY,
T3.ADDEDON,
'T3' tableID
FROM Table3 T3
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With