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

Navigation
1

EXAMPLE :

I have two tables:

  • table1 : with two columns product_id , product_name ;
  • table2 : With three columns tag_name , product_id , tag_name

I would like to create a table3 from a selection of table1 and table2 with only the columns product_id , product_name , and brand_name . 


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
)

In this way I can get almost what I want, so that table3 is created already filled with the data of the 1 strong> 2 . What I would like to get is a table with structure based on the selection of the other tables.

I tried another way (using 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.

Is there another way to do this?

    
asked by anonymous 09.12.2016 / 13:58

3 answers

4

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 in SQL Fiddle .

  • The WHERE false causes the procedure not to return on one line, leaving the new table empty.

  • Do not forget to use ALIAS in cases where you may have names ambiguity, to avoid problems.

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

    FROM tabela1 p, tabela2 m

    (it's okay to keep the original as well, both work)


If you want to have more control over the procedure

You can execute this command on tables, and retrieve column types to redo CREATE manually, it is almost always preferable: 

SHOW CREATE TABLE meubanco.minhatabela;

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

    
09.12.2016 / 16:26
0

With TOP 0 you 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
    
09.12.2016 / 14:56
0

Hello! Well, I know the code is ugly. But on a quick test here, that's what you're looking for. See, if the number of fields is greater than the current three, then better refactor. Hugs! 

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, really correct form reported 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);
    
09.12.2016 / 15:06
Not returning the second parameter How to Order List (Hibernate)