Simplest way to merge two or more tables

Question

Simplest way to merge two or more tables

garethmann101

SSCommitted

Points: 1980
More actions
October 29, 2008 at 12:41 pm

#195472

I have a set of tables with 4 identical fields. I need to have the data for all tables merged into a single table, note this is NOT a join.
Say each of the 4 tables has 100 records, the output will be one table with 400 records. I was hoping there was a simple SQL command to do this. I am not sure how to search for it because I am not sure if "merge" is the right nomenclature to search for.
i.e. something like:
Merge(Table1, Table2, Table3, etc.)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Just a little more info. Are all four tables completely identical, or are there only 4 fields that are identical? If there are other fields that aren't, do you need those as well? In the merged table, do you need to know from which table each row came from?

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

garethmann101 (10/29/2008)
I have a set of tables with 4 identical fields. I need to have the data for all tables merged into a single table, note this is NOT a join.
Say each of the 4 tables has 100 records, the output will be one table with 400 records. I was hoping there was a simple SQL command to do this. I am not sure how to search for it because I am not sure if "merge" is the right nomenclature to search for.
i.e. something like:
Merge(Table1, Table2, Table3, etc.)

In 2008 this can be accomplished using MERGE - i.e.

CREATE TABLE DataTable1 (ID int, DataValue varchar(50), Msg varchar(50))

CREATE TABLE DataTable2 (ID int, DataValue varchar(50), Msg varchar(50))

GO

INSERT INTO DataTable1 VALUES (1, 'TDV1', 'Inserted'), (2, 'TDV2', 'Inserted')

INSERT INTO DataTable2 VALUES (1, 'TDV3', 'Inserted'), (3, 'TDV4', 'Inserted')

GO

MERGE DataTable2 AS mainData

USING (SELECT ID, DataValue FROM DataTable1) otherData

ON (mainData.ID = otherData.ID)

WHEN MATCHED THEN UPDATE SET Msg = 'MATCHED'

WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, DataValue, 'TARGET NOT MATCHED')

WHEN SOURCE NOT MATCHED THEN UPDATE SET Msg = 'SOURCE NOT MATCHED';

GO

In 2005 UNION ALL - i.e.

SELECT *

INTO NewDataTable

SELECT ID,DataValue,MSG

FROM DataTable1

UNION ALL

SELECT ID,DataValue,MSG

FROM DataTable2

Tommy

Follow @sqlscribe

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Tommy, don't think that is what (MERGE in SQL 2008) the OP is looking for. Original post indicated 4 tables with 100 records each, into one table with 400 records.

Your second option sounds more likely:

insert into CombinedTable (field1, field2, field3, field4)

select

field1, field2, field3, field4

from

table1

union all

select

field1, field2, field3, field4

from

table2

union all

select

field1, field2, field3, field4

from

table3

union all

select

field1, field2, field3, field4

from

table4

garethmann101 SSCommitted Points: 1980 More actions · Answer 4

Thanks Lynn, i will try that. Is there any reason why "Union All" comes after the first three tables but not the final table?

And to answer your first post, all fields are identical and there are no additional fields. I.e. all tables have only 4 identical fields

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442467

October 29, 2008 at 1:26 pm

#891444

Because it is the last table.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

UNION [ALL] is an "binary infix" operator, which means that it works like addition (+) and multiplication (*):

{Select statement} UNION [ALL] {Select statement} UNION [ALL] {Select statement} ...

So you only put the "UNION"'s between the Select statements. The "ALL"'s are optional, without them you get an implicit DISTINCT.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

garethmann101 SSCommitted Points: 1980 More actions · Answer 7

Thanks for that, that worked perfect. The only additional thing needed was to create the empty table before hand.