How to create a table (empty) from a selection of other tables?

Question

EXAMPLE:

I have two tables :

• Tabela1 : two-column id_product, product name;
• table 2 : three-column id_brand, id_product, tag

I would like to create a Tabela3 from a selection of the Tabela1 and table 2 with only the columns id_product, product name and tag.

CREATE TABLE tabela3 IF NOT EXISTS (
    SELECT p.id_produto, p.nome_produto, m.nome_marca
    FROM tabela1 p
    JOIN tabela2 m 
    ON p.id_produto = m.id_produto
)

Doing it that way I can get almost what I want, only that the Tabela3 is created already filled with table data 1 and 2. What I would like to get is a table made with the structure based on the selection of the other tables.

I tried another way ( using the LIKE):

CREATE TABLE tabela3 IF NOT EXISTS LIKE(
   SELECT p.id_produto, p.nome_produto, m.nome_marca
    FROM tabela1 p
    JOIN tabela2 m 
    ON p.id_produto = m.id_produto
)

I got a nice SQL syntax error.

There’s another way to do it ?

Answer 1

To make "automatic":

Create a condition that does not return records:

CREATE TABLE tabela3 (
    SELECT p.id_produto   AS id_produto,
           p.nome_produto AS nome_produto,
           m.nome_marca   AS nome_marca
    FROM tabela1 p, tabela2 m
    WHERE false
);

See working on SQL Fiddle.

• The WHERE false causes the procedure not to return a row, leaving the new table empty.

• Do not forget to use ALIAS in cases where there may be ambiguity of names, to avoid problems.

• Since you are not using the data, you can simplify JOIN this way:

  FROM tabela1 p, tabela2 m
  

  (it has no problem to keep as is the original too, both work)

If you want to have more control over the procedure

You can execute this command in the tables, and recover the column types to redo the CREATE manually, almost always is preferable:

SHOW CREATE TABLE meubanco.minhatabela;

Then you copy the column settings you want, in the order that is best for your new table, and adjust everything you need.

Answer 2

With TOP 0 you will only have the definition of the columns, without their values.

SELECT TOP 0 p.id_produto, p.nome_produto, m.nome_marca INTO tabela3 
FROM tabela1 p
INNER JOIN tabela2 m 
ON p.id_produto = m.id_produto

If you want to check if the table exists enter the following conditional

IF OBJECT_ID('tabela3') IS NULL
BEGIN
   SELECT TOP 0 p.id_produto, p.nome_produto, m.nome_marca INTO tabela3 
   FROM tabela1 p
   INNER JOIN tabela2 m 
   ON p.id_produto = m.id_produto
END

Answer 3

Hello! Well, I know the code is bad. But in a quick test here, it has fulfilled what you’re looking for. See, if the number of fields is greater than the current three, then it is better to refactor. Hug!

CREATE TABLE tabela3 (
 SELECT p.id_produto, p.nome_produto, m.nome_marca
 FROM tabela1 p
 JOIN tabela2 m 
 ON p.id_produto = m.id_produto
 WHERE p.id_produto IS NULL 
  AND p.nome_produto IS NULL
  AND m.nome_marca IS NULL
);

But see the second way, really correct informed by our friend Ls_dev in the comments, using (WHERE FALSE)

CREATE TABLE tabela3 (
SELECT p.id_produto, p.nome_produto, m.nome_marca
FROM tabela1 p
JOIN tabela2 m 
ON p.id_produto = m.id_produto
WHERE FALSE);