Consolidating two Identical tables into one table SQL Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Consolidating two Identical tables into one table SQL Query?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-22 : 17:09:15

Mark writes "How do you consolidate the data from two identical tables into one table using a SQL Query?

For example, I have an archive database and a current database. The tables in question have the same fields but all the data is different. There is no way to link the tables in a "from to" sense. I just need all the data in one table keeping the field names the same.

Table one:
Firstname LastName
Fred Jones

Table two:
Firstname Lastname
John Smith

New Table:
Firstname LastName
Fred Jones
John Smith"

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-22 : 17:14:59

use a union statement.

select * from table1
union
select * from table2

union will weed out any duplicates, if you want dupes use "union all"

hth,
Justin

Mpitts
Starting Member

2 Posts

Posted - 2002-01-22 : 17:30:32

I was aware of the UNION statement. I should have outlined my problem a little deeper.

I am using ODBC datasources. I can use either the User DSN or the File DSN whichever I can get to work. The problem I am encountering is that the tables I am trying to access have the same name so there needs to be a way to distinguish the tables.

For example

SELECT *
FROM Table
IN (ODBC Data Source A)
UNION ALL
SELECT *
FROM Table
IN (ODBC Data Source B)

I have looked everywhere and have found syntax for Dbase and Paradox, but nothing for ODBC data sources.

Is there a way to distinguish ODBC data sources in SQL??

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-22 : 17:40:00

Are these tables both in SQL Server? Are they on different servers, in different databases, or just have different owners?

If they are on different servers you can define a linked server, and use the 4 part naming convention

[Server].[Database].[Owner].[Table]

This is how you distinguish 2 tables with the same name. It is impossible to have 2 tables with the same name on the same server, in the same database with the same owner.

HTH
-Chad

Mpitts
Starting Member

2 Posts

Posted - 2002-01-22 : 17:54:00

Thanks Chad, I feel I am going in the right direction. The Tables reside on the same server in different Databases with the same owner. I think I should point out that they are in Lotus NotesSQL (*.nsf). Is it possible to provide a specific example of the four part naming convention?

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-22 : 17:57:17

Well,

I'm not sure about lotus, but in SQL Server it would be:

SELECT *
FROM Server.Database1.owner.Table
UNION ALL
SELECT *
FROM Server.Database2.owner.Table

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources