Question: Im trying to add data from tables into one table using this SQl below:

```
INSERT INTO myNewTable
SELECT *
FROM 2010-tbl, 2011-tbl, 2013-tbl
WHERE fldName = 'value';

```

All the tables have exactly the same structure - but different values in each. The first table(myNewTable) has no values and will accept all values inserted into it by the others. So how do i go about doing this? Do I need a JOIN statement of some sort?

***EDIT***

Just one thing - Im trying to remove at previous records in it before the insert - but MS Access is throwing this error:

```
Characters found after end of SQL statement

```

This is the code:

```
DELETE * FROM myNewTable
INSERT INTO myNewTable
SELECT * FROM 2012-tbl WHERE  (((2012-tbl.[field])=[Text]));

```

Thanks

Answer: 
Use a `Union` in your `SELECT` Statement

```
INSERT INTO myTable 
SELECT * 
FROM 2010-tbl 
WHERE fldName = 'value'
UNION
SELECT * 
FROM 2011-tbl 
WHERE fldName = 'value'
UNION
SELECT * 
FROM 2013-tbl 
WHERE fldName = 'value'

```
Simple way to do this is to use **UNION ALL** with **SELECT**.
If you want to eliminate duplicate rows, use UNION instead of UNION ALL.

```
DELETE * FROM   mynewtable;

INSERT INTO mytable 
SELECT * FROM 2010-tbl WHERE fldname = 'value' 
UNION ALL 
SELECT * FROM 2011-tbl WHERE fldname = 'value' 
UNION ALL 
SELECT * FROM 2012-tbl WHERE  fldname = 'value';

```

This works fine if these tables have less number of records, but if these tables contains large number of records then it gives ***heap memory related error***.
To avoid this you can use following modular snippet to get your job done!

```
DELETE * FROM mynewtable;

INSERT INTO mytable SELECT * FROM 2010-tbl WHERE fldname = 'value';

INSERT INTO mytable SELECT * FROM 2011-tbl WHERE fldname = 'value';

INSERT INTO mytable SELECT * FROM 2012-tbl WHERE fldname = 'value';

```

If you have too many tables then using above(2nd way) may be time consuming as you have to enter each command one by one, So to make it easy you can put this code snippet in one text file and then use source command to run these commands at once. Example:

1. create file with name **allCommands** and put these commands into
it:
 $ cat > allCommands
 DELETE \* FROM mynewtable;

```
INSERT INTO mytable SELECT * FROM 2010-tbl WHERE fldname = 'value';

INSERT INTO mytable SELECT * FROM 2011-tbl WHERE fldname = 'value';

INSERT INTO mytable SELECT * FROM 2012-tbl WHERE fldname = 'value';

```
2. Start ***mysql*** from terminal/cmd

```
shell> mysql

```
3. Use ***source*** command to run this script. ***Don't forget to give proper path of allCommands file.*** 

```
mysql> use db_name;
mysql> source allCommands;

```