October 29, 2008 at 12:41 pm
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.)
October 29, 2008 at 12:49 pm
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?
October 29, 2008 at 12:56 pm
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 @sqlscribeOctober 29, 2008 at 1:01 pm
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
October 29, 2008 at 1:20 pm
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
October 29, 2008 at 1:26 pm
Because it is the last table.
October 29, 2008 at 1:41 pm
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]
November 3, 2008 at 3:13 pm
Thanks for that, that worked perfect. The only additional thing needed was to create the empty table before hand.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply