3

I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a VIEW like behavior. Let me illustrate by a fake example that as far as I know would not work but just to properly explain what I would like to achieve.

Let us say I have these two tables:

CREATE TABLE table_1 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [d] [bigint] NULL,
)
GO

CREATE TABLE table_2 (
    [a] [bigint] NOT NULL,
    [b] [bigint] NOT NULL,
    [c] [varchar] (32) NULL,
    [e] [varchar](256) NULL,
)
GO

Where a, b, c are equivalent but hold different data. d exists in table_1 but not in table_2 and e exists in table_2 but not in table_1.

Tables do not hold any common data. That is, a JOIN on any field would bring zero results.

I would like to be able to do the following (Or something equivalent rather. I know what I am showing is not doable):

CREATE VIEW MyUnion FROM(
SELECT * FROM table_1 UNION SELECT * FROM table_2) AS alldata;

In such a way that if I run:

SELECT * FROM MyUnion;

And would get something like (1 and One are just filler representing a generic number or string):

a       b       c       d       e
1       1       One     1       NULL
1       1       One     NULL    One

Is there any way to do anything like this without duplicating the data? (i.e. creating another table holding everything)

Thank you!

7

Something like this?:

CREATE VIEW MyUnion AS
SELECT
    a, b, c,
    d,
    CAST(NULL AS varchar(32)) AS e 
FROM table_1 
UNION ALL
SELECT
    a, b, c, 
    CAST(NULL AS bigint) AS d,
    e
FROM table_2 ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.