-1

There are multiple tables. All tables have the same column names with different values. I have created one new table with the same columns. I want to put all those data into this newly crated single table. There is no unique id in any of the tables. I just want to merge all table data into one sinlge table.

Table names are - Andra Pradesh, Arunachal Pradesh, Assam, Bihar, Chhattisgarh, Delhi, Hariyana, Himachal Pradesh, Karnataka.

All column names are the same in all the tables. Here are the column names - State, District, Year, January, February, March, April, May, June, July, Aug‌​ust, September, Octobe‌​r, November, December. The new table which I created with the same columns is called tbl_India. I want to add those data into this new table.

4

You can use an INSERT ... SELECT statement for that:

INSERT INTO
  tbl_India (State, District, Year,
             January, February, March, April, May, June,
             July, Aug‌​ust, September, Octobe‌​r, November, December)

SELECT
  State, District, Year,
  January, February, March, April, May, June,
  July, Aug‌​ust, September, Octobe‌​r, November, December
FROM
  "Andra Pradesh"

UNION ALL

SELECT
  State, District, Year,
  January, February, March, April, May, June,
  July, Aug‌​ust, September, Octobe‌​r, November, December
FROM
  "Arunachal Pradesh"

UNION ALL

…  -- repeat for all the other tables
| improve this answer | |
  • its showing the erro like "Error converting data type varchar to float". – shivanand sb Sep 16 '16 at 6:41
  • 3
    That probably means that your target table's structure doesn't exactly match that of the source table(s). Look at your table definitions, compare the column types in order to find the offending column (or columns). – Andriy M Sep 16 '16 at 6:44
2

Here you go. Try Option A or Option B:

Option A

select * into mynewtable from
(
select x,y,c from mytable1
union --removes duplicates
select x,y,c from mytable2
) t1

or

Option B

select * into mynewtable from
(
select x,y,c from mytable1
union all --does not remove duplicates
select x,y,c from mytable2
) t1
| improve this answer | |
  • post your table names and column names you're trying to merge in your original question and I'll take a look at it tomorrow. – Sting Sep 16 '16 at 3:32
  • Table names are - Andra Pradesh, Arunachal Pradesh, Assam, Bihar, Chhattisgarh,Delhi,Hariyana,Himachal Pradesh, Karnataka. – shivanand sb Sep 16 '16 at 3:37
  • And all column names are same in all the tables , here is the column names - State, District,Year,January,February,March,April,May,June,July,August,September,October,NovemberDecember. And tbl_India this is the new table which I created with the same columns.... I want to add those all data in this new table. – shivanand sb Sep 16 '16 at 3:40
2

Do you really need to copy the data? By defining a view the data can stay where it is but the benefits of a single, unified point of access can be achieved. The view can be defined using either UNION or UNION ALL syntax as appropriate.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.