Appending Tables from the Prepare Window

From the Prepare Window, you can append tables by selecting them from the Table List (use Ctrl+click to select more than one table), right-clicking on your mouse, and then selecting an Append operation from the context menu that displays.

Note that:

Different options will produce different results.
In some cases, the order in which you select tables to append will affect the result.

Steps

In the Prepare window table list, select the tables you want to append. (Ctrl+click on the tables to select more than one table.)
Right click, select Append Tables, and the then select the append method:

Match Columns on Name
Match Columns on Name and Type
Match Columns on Order
Match Columns on Order, Name and Type

Note: If you want more control with defining the append, consider using the Edit Append dialog.

Examples: Appending Tables by Matching on Name

Example 1

Consider the following tables to append:

Data Processing Table

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

Matching Columns on Name produces the following result:

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
	April	Anne	1597429	10/23/1993 0:00	F
	Banning	David	1607768	12/18/2003 0:00	M
	Aldridge	Jeff	1592784	10/13/1995 0:00	M
	Daley	William	1587390	1/30/1993 0:00	M
	Georges	William	1596792	6/4/2005 0:00	M

In this example, the Department column values of the Marketing and Accounting tables are not included because they do not match the name of the first column in the first table.

Note that Monarch Data Prep Studio will consider the first table as the basis for appending succeeding tables.

Example 2

If you append tables in a different order, i.e.:

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

Data Processing Table

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

You get this result:

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M
	Bass	Andrew	1593211	3/18/1999 0:00	M
	Bittner	Herb	1597596	3/18/2005 0:00	M
	Bittner	Martha	1604193	12/18/2001 0:00	F

Example 3

Note that the position does not matter when you append by Matching Columns on Name:

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

Data Processing Table

Last Name	First Name	Emp ID	Date	Department	Gender
Bass	Andrew	1593211	3/18/1999 0:00	Data Processing	M
Bittner	Herb	1597596	3/18/2005 0:00	Data Processing	M
Bittner	Martha	1604193	12/18/2001 0:00	Data Processing	F

You get this result:

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Examples: Appending Tables by Matching on Name and Type

When you Match on Name and Type, Monarch Data Prep Studio will append only tables with columns that have the same name and data type. This append follows the same logic as Matching in Name, but with the added condition that column data types match.

Example 1

Consider the following tables to append:

Data Processing Table

Department	Last Name	First Name	Emp ID (number)	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department	Last Name	First Name	Emp ID (text)	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID (text)	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

The result will be::

Department	Last Name	First Name	Emp ID (number)	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
Marketing	April	Anne		10/23/1993 0:00	F
Marketing	Banning	David		12/18/2003 0:00	M
Accounting	Aldridge	Jeff		10/13/1995 0:00	M
Accounting	Daley	William		1/30/1993 0:00	M
Accounting	Georges	William		6/4/2005 0:00	M

In this example, the Emp ID column values of the Marketing and Accounting tables are not included because they do not match the data type of the Emp ID column in the Data Processing table.

Example 2

If you have the following tables:

Data Processing Table

Department	Last Name	First Name	Emp ID (number)	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department	Last Name	First Name	Emp ID (text)	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Employee ID (number)	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

The result will be::

Department	Last Name	First Name	Emp ID (number)	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
Marketing	April	Anne		10/23/1993 0:00	F
Marketing	Banning	David		12/18/2003 0:00	M
Accounting	Aldridge	Jeff		10/13/1995 0:00	M
Accounting	Daley	William		1/30/1993 0:00	M
Accounting	Georges	William		6/4/2005 0:00	M

In this example, the Emp ID column values of the Marketing and Accounting tables are not included because they do not match either the name or the data type of the Emp ID column in the Data Processing table.

Example: Appending Tables by Matching on Order

When you match on Order, Monarch Data Prep Studio will append only tables with columns that match on position. Monarch Data Prep Studio will ignore column names and data types.

Example

Consider the following tables to append:

Data Processing Table

Dept	Last Name	First Name	Emp ID (number)	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department

Last Name

First Name

Emp ID

(number)

Date

Gender

Marketing

April

Anne

1597429

10/23/1993 0:00

Marketing

Banning

David

1607768

12/18/2003 0:00

Accounting Table

Department	Last Name	First Name	Emp ID (text)	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

If:

The Department column of the Data Processing table has a different name, and
The Emp ID column of the Accounting table has a different data type (text instead of number)

The result will be:

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

In this example, all values of Emp ID from all tables are included since we are only matching on Order and Name.

Appending Tables by Matching on Order, Name, and Type

When you Match on Order, Name, and Type, Monarch Data Prep Studio will append only tables with columns that match on position, name, and type.

Example 1

Consider the following tables to append:

Data Processing Table

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

If columns in all tables have matching data, types, Append Tables > Match on Order, Name, and Type result will be:

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
	April	Anne	1597429	10/23/1993 0:00	F
	Banning	David	1607768	12/18/2003 0:00	M
	Aldridge	Jeff	1592784	10/13/1995 0:00	M
	Daley	William	1587390	1/30/1993 0:00	M
	Georges	William	1596792	6/4/2005 0:00	M

In this example, the Department column values of the Marketing and Accounting tables are not included because they do not match the name of the first column in the first table.

Note that Monarch Data Prep Studio will consider the first table as the basis for appending succeeding tables. If you append tables in a different order, i.e.:

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

Data Processing Table

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

You get this result instead:

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M
	Bass	Andrew	1593211	3/18/1999 0:00	M
	Bittner	Herb	1597596	3/18/2005 0:00	M
	Bittner	Martha	1604193	12/18/2001 0:00	F

Example 2

Consider the following tables to append:

Data Processing Table

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F

Marketing Table

Department	Last Name	First Name	Emp ID	Date	Gender
Marketing	April	Anne	1597429	10/23/1993 0:00	F
Marketing	Banning	David	1607768	12/18/2003 0:00	M

Accounting Table

Department	Last Name	First Name	Emp ID	Date	Gender
Accounting	Aldridge	Jeff	1592784	10/13/1995 0:00	M
Accounting	Daley	William	1587390	1/30/1993 0:00	M
Accounting	Georges	William	1596792	6/4/2005 0:00	M

If:

The Department column of the Data Processing table has a different name, and
The Emp ID columns of the Accounting table has a different data type (text instead of number)

An Append > Matching on Order, Name, and Type result will be:

Dept	Last Name	First Name	Emp ID	Date	Gender
Data Processing	Bass	Andrew	1593211	3/18/1999 0:00	M
Data Processing	Bittner	Herb	1597596	3/18/2005 0:00	M
Data Processing	Bittner	Martha	1604193	12/18/2001 0:00	F
	April	Anne	1597429	10/23/1993 0:00	F
	Banning	David	1607768	12/18/2003 0:00	M
	Aldridge	Jeff		10/13/1995 0:00	M
	Daley	William		1/30/1993 0:00	M
	Georges	William		6/4/2005 0:00	M

Appending Tables from the Prepare Window

Steps

Examples: Appending Tables by Matching on Name

Example 1

Example 2

Example 3

Examples: Appending Tables by Matching on Name and Type

Example 1

Example 2

Example: Appending Tables by Matching on Order

Example

Appending Tables by Matching on Order, Name, and Type

Example 1

Example 2

Related Links