Query to consoldate tables - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query to consoldate tables

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-24 : 07:55:25

I have two tables, they have most fields in common however there are fields in table1 which are not in table2 and vice versa.

Is there a query I can use to get all records from one table and all records from the other.

I want the result set to contain all fields from both tables.

What i'm looking for really is a UNION ALL type result but using tables with different fields.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 08:01:15

If both table have matching records, meaning records in table1 exists in table2 and vice versa, you can just use INNER JOIN


SELECT t1.col1, t1.col2, t1.col3, t2.col4, t2.col5, t1.col6
FROM   table1 t1 INNER JOIN table2 t2
ON     t1.pk = t2.pk

KH
[spoiler]Time is always against us[/spoiler]

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-24 : 09:41:15

The records in T1 dont relate in any way to T2, what I mean is there is no primary/foreign key relationship between them

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-24 : 09:45:59

Example

Table1

CDID Make Model Engine

2087 Ford Mondeo 1.8
7765 Audi A4 2.0
7676 BMW 318i 1.9

Table 2

Make Model Derivative Customer

Audi A6 1.8 SE 5dr SMITH
Cit C5 2.0S JONES

Desired Results

CDID Make Model Engine Derivative Customer
2087 Ford Mondeo 1.8 NULL NULL
7765 Audi A4 2.0 NULL NULL
7676 BMW 318i 1.9 NULL NULL
NULL Audi A6 NULL 1.8 SE 5Dr SMITH
NULL CIT C5 NULL 2.0S JONES

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-24 : 09:47:16

you just want a result set that combines all records of both table ?

SELECT col1, col2, col3, col4 = NULL, col5 = NULL, col6
FROM   table1

UNION ALL

SELECT col1 = NULL, col2 = NULL, col3 = NULL, col4, col5, col6 = NULL
FROM   table2

KH
[spoiler]Time is always against us[/spoiler]

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-24 : 11:23:42

Thats it - thanks. Didn't realise you could put blanks in the select statement to satisfy the requirements of UNION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources