Member Avatar for freiheit

Hello all :)

Me and a friend of mine we are working in a project at the moment.

We populate our database each one with his own data but now we need to merge our tables.
The database has the same name in both machines, and also table name.

I dont want to display the date on the browser just by quering the 2 tables, i want to create a new table which it will be the data of the other 2 ones.

For example i have came up to this

Select * FROM kiniseis
UNION ALL
Select * FROM kiniseis1

Somewhere i should put Create but i dont know the right syntax :(
But this is just a query that i dont want to use, i need to have the new table in a "physical" form that can be explored in Mysql Administrator for example.

Can someone pls help me?

Thank you :)

Edited by freiheit because: n/a
  • 1 Contributor
  • 2 Replies
  • 100 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by freiheit

Dani AI

Generated

Short answer: create the destination table with the AUTO_INCREMENT column in its definition, then insert the combined rows while explicitly listing columns so MySQL will generate new IDs (or preserve originals in a separate column).

As found, a combined SELECT/UNION gives the rows but does not by itself create a nicely defined physical table with an AUTO_INCREMENT column you can inspect in MySQL Administrator. Two safe patterns:

  1. Create the merged table with the schema you want (including AUTO_INCREMENT), then pull rows from both sources into it. Example pattern:
CREATE TABLE merged (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  orig_id INT,
  colA VARCHAR(100),
  colB INT,
  src CHAR(1)
);

INSERT INTO merged (orig_id, colA, colB, src)
SELECT id, colA, colB, 'A' FROM sourceA
UNION ALL
SELECT id, colA, colB, 'B' FROM sourceB;

This preserves the original id in orig_id and gives each row a new id. If you want new IDs only, omit orig_id and do the INSERT without the source id column.

  1. Copy the exact schema first, then load data. This keeps indexes and column attributes:
CREATE TABLE merged LIKE sourceA;
ALTER TABLE merged ADD COLUMN src CHAR(1);

INSERT INTO merged (colA, colB, src) SELECT colA, colB, 'A' FROM sourceA;
INSERT INTO merged (colA, colB, src) SELECT colA, colB, 'B' FROM sourceB;

Notes and tips:

  • Always specify column lists; avoid SELECT *.
  • If you insert explicit values into an AUTO_INCREMENT column, run an ALTER TABLE merged AUTO_INCREMENT = N afterwards so the next generated id is correct.
  • For large loads, drop nonessential indexes before the import and rebuild them after for speed.
  • If the two source databases are on different machines, export/import (dump or CSV) or connect remotely and run the inserts.
  • Backup first and test on a copy before touching production data.
Member Avatar for freiheit

I came up to this

CREATE TABLE kiniseisAll SELECT * FROM (SELECT * FROM kiniseis UNION ALL  SELECT * FROM kiniseis1) AS kiniseisAll

But i can not figure out how to write the correct suntax for the auto increment id in the query :(

Member Avatar for freiheit

Anyone?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.