SQL: Combine two tables without a 'Join on'

Question

So I have two tables with only a couple of similar columns that I need to join to give them a common sequencing, but without combining any of the data.

For example:

Table Foo:

AccountID   Date        FooType
--------------------------------
1234        12/24/15    A
1234        12/12/15    A
5464        01/04/15    A

Table Bar:

AccountID   Date        BarAmount   BarFlag
--------------------------------------------
1234        05/03/15    12.56       1
7657        02/14/15    5.11        0
9879        01/04/15    8.00        1

The goal is to get the output to be:

AccountID   Date        BarAmount   BarFlag    FooType
-------------------------------------------------------
1234        05/03/15    12.56       1          NULL
1234        12/24/15    NULL        NULL       A
1234        12/12/15    NULL        NULL       A
7657        02/14/15    5.11        0          NULL
9879        01/04/15    8.00        1          NULL
5464        01/04/15    NULL        NULL       A

So in other words, I don't want to combine by joining on the AccountID (that would give me 4 or 5 rows depending on how I set it up) but I do want the columns to be populated when they are named the same. (there is some crossover between the tables)

A UNION ALL gives me an error because the tables, while having some columns in common, are very different. The error it gives is: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." Some articles claim this can be bypassed by specifying column names. I tried that (query below) and it still gave me the error.

SELECT [AccountID],[Date],[BarAmount],[BarFlag] FROM Bar 
UNION ALL
SELECT [AccountID],[Date],[FooType] FROM Foo

An alternative that occurred to me is to insert them into a new table but since the columns are ones that are derived from other queries, the columns may need to change. So I cannot just create a table to use, I would have to do it on the fly (use a temp table or drop it after I am done, something like that). The challenge there is that I cannot seem to figure out how to dynamically create the additional columns I need. For example, if I use this to start:

SELECT * INTO NewTable FROM Bar 

Then I would have all the records from Bar, and SQL helpfully creates the columns for me. However, when I want to get the records from Foo, I cannot do another INSERT INTO because the table is already there. I can do an INSERT INTO, but how do I add the extra columns in that don't exist in the NewTable without specifying their name and datatype in an ALTER TABLE statement? (Since I won't know what their names and datatypes are necessarily, that could prove difficult)

So any help would be appreciated, I don't care how I get to the 'goal' listed above, as long as I get there. Thank you for your time.

Edit:

That did it thank you! Sometimes its the smallest of things that make the difference. One thing to note for those that may reference this question in the future: the order matters! I built my query based on this answer and started getting an error. The error: "Conversion failed when converting date and/or time from character string." was because instead of the above query, I did something similar to this:

SELECT **[Date]**,[AccountID],[BarAmount],[BarFlag],NULL as FooType FROM Bar 
UNION ALL
SELECT [AccountID],**[Date]**,NULL,NULL,[FooType] FROM Foo

It was trying to put text into the date column and couldn't reconcile it, so it wouldn't work. When the columns are a common one, the order matters (and it is only logical). Thank you again sgeddes!

Answer 1

You were close -- the union all needs to have the same number of columns. Try this:

SELECT [AccountID],[Date],[BarAmount],[BarFlag],NULL as FooType FROM Bar 
UNION ALL
SELECT [AccountID],[Date],NULL,NULL,[FooType] FROM Foo