How to pass data from one table to another SQL

Question

I am doubtful to pass data from one table to another: Example:

Table 1 --> Table 2 ---> delete all information from Table 1

I am creating the project in VB.NET and using SQL

Answer 1

If the second table already exists use INSERT INTO SELECT

If both tables have the same fields:

INSERT INTO table2
SELECT * FROM table1;

If only some fields are common, you will need to specify these fields:

INSERT INTO table2
(column_name1, column_name2, ...)
SELECT column_name1, column_name2, ...
FROM table1;

Note: the field names may be different, but they must be of the same type. They will be filled in according to the order in which they are declared.

In both cases the clause may be used WHERE:

INSERT INTO table2
(column_name1, column_name2, ...)
SELECT column_name1, column_name2, ...
FROM table1
WHERE column_name1 = "a qualquer coisa";

To delete the table use:

DROP TABLE tabela1

Answer 2

Basically, speaking:

Creates a table tabela_2 based on tabela_1:

CREATE TABLE tabela_2 AS SELECT * FROM tabela_1

Delete the copied table.

DROP TABLE tabela_1

Note: chaves-primárias and chaves-estrangeiras are not replicated in tabela_2, if they exist in the tabela_1. Not even the AUTO_INCREMENT is copied.

Updating

It is important to post here that I have found a better and more appropriate way as to the problem of not being able to copy primary and foreign keys.

There’s a way to do that through LIKE. Instead of just copying the table, ignoring the keys, it literally copies the table, with keys and everything else.

Behold:

CREATE TABLE tabela_3 LIKE tabela_1;
INSERT INTO tabela_3 SELECT * FROM tabela_1;

In this case, we executed two queries. It is not a disadvantage, since you will have the keys identical to the original copied table.