Union join with tables of unknown structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Union join with tables of unknown structure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-06 : 08:35:30

Daniel writes "Hello! I repeatedly have to concatenate tables with different structure. For example:

OldTable1


id  f1  f2  f3
1   3   2   3
2   1   3   1
3   1   2   2

OldTable2


id  f3  f4  f5
1   3   2   3
2   5   5   2
3   5   3   2

I'd like to have the following be the result of the concatenation:

NewTable


id  f1   f2   f3   f4   f5
1   3    2    3    .    .
2   1    3    1    .    .
3   1    2    2    .    .
4   .    .    3    2    3
5   .    .    5    5    2
6   .    .    5    3    2

where '.' means NULL. Note that the "id" field of NewTable is auto incremented.

SAS (see http://www.sas.com) does this no problem. What's the easy/lazy way of doing it in SQL Server?

The UNION operator requries that I know the structure of each table, and that I structure my UNION query appropriately with a bunch of "SELECT NULL as f4", etc. However, many of my tables have 50 to 100 columns, and I don't want to write big, ugly queries. Any thoughts appreciated.

Daniel"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-06 : 09:16:46

I don't think you need a UNION ... rather you should look at a JOIN.

Where did id in (4,5,6) come from in your example?

Jay White
{0}

slacker
Posting Yak Master

115 Posts

Posted - 2003-02-06 : 22:15:43

It looks as if your trying to stack tables on top of one another matching up the fields with the same name. If this is true a join wont work. I think if you dont want to have to write out the sql statement you should think about creating a stored procedure that can generate the sql statement for you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-07 : 01:34:56

quote:

many of my tables have 50 to 100 columns

Are you tables normalized?

The easy/lazy way would be to have your tables normalized and stop having to repeatedly concatenate tables with different structures.

Having said that you can create an insert statement that inserts
f1,f2,f3,f4,f5 into new table or temp table with f4 and f5 being null this can be designed with view designer (LAZY) and cut and pasted into stored procedure.

Then design second insert into table with
f1,f2,f3,f4,f5 where f1 and f2 are Null or just modify first statement very easy.

Then select * from the new table

This way you don't need to know the columns and be LAZY.

Next pray you database remains small so performance is not a factor and you can continue to be lazy or the task is given to some other poor soul who will hate your guts.

Edited by - ValterBorges on 02/07/2003 01:36:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources